Hi, I have 50 customers on drop down and months on another drop down. I need a cell to be populated with unique invoice number for a certain customer for a certain month. I have tried the formula:
=IF(AND(A1="Cust A";A2="January";"CustA001")).
I have to do this for all 50 customers for january only. Is there a way I can use arrays maybe?
Please help.
Last edited by Mcambi; 02-19-2018 at 10:18 AM.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Welcome to the forum! Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!
- Use concise, accurate thread titles.
- Your post title should describe your problem, not your anticipated solution.
- Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
- Responding to a request to change your thread title by doing so is mandatory.
To change a title go to your first post, click EDIT then Go Advanced and change your title.
No help to be offered, please, until the OP complies with this request.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
TMS,
I need to select customer name and month from drop down at B3 and B4 respectively and B5 will be populated with correct invoice number.
I have only done for customer 1 for January and February only.
I need to do it for 50 customers for different months. Is there a better way?
I think months might be better as a number but
=B2&TEXT(MONTH(DATEVALUE("1-"&B3& "99")),"000")
or to lose the space
=SUBSTITUTE(B2," ","")&TEXT(MONTH(DATEVALUE("1-"&B3& "99")),"000")
I wasn't quick enough following up .... davsth beat me to the answer.
For what it's worth, I also went withFormula:
=SUBSTITUTE(B2," ","") & TEXT(MONTH(DATEVALUE("1"&B3&" 2018")),"000")
The year can be anything you like, from 1900 on. In fact, you can just put 0, thus:Formula:=SUBSTITUTE(B2," ","") & TEXT(MONTH(DATEVALUE("1"&B3&" 0")),"000")
You're just trying to built something that the DATEVALUE function will interpret as a date so that you can then use MONTH to extract the month and thus convert it to a number. You then use the TEXT function to format the month number as 3 digits. That said, it seems a little pointless having three digits if the Invoice number is based on the month.
davsth & TMS
You guys are amazing! Worked like a charm.
Thank you so much
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks