auto number incl. text and date over multiple sheets

[LauraB2013]

Hello I have a file with multiple sheets for each day of the month to track daily sales...each sale/order is given a unique reference number. This reference number includes the sales person's unique prefix (2 letters), followed by a consecutive 2 digit number - e.g. sales person's first order for the day is 01, followed by 02..etc, and then the date in the format ddmmyy, and sometimes a suffix for the sales person's location (2 letters). for example... AB##ddmmyyAB. This reference number needs to be entered into the lefthand column on each worksheet everyday and I am wondering if it is possible for excel to automatically create the reference number if the sales person enters their prefixes/suffixes? each file is only used by the one sales person so only has the one set of prefixes/suffixes. I have tried quite a few ways of doing it...but they are quite time consuming when repeating across the multiple sheets, for multiple people with different details. Excel doesn't pick up the pattern so won't auto-fill with the consecutive number either. I hope this makes sense...each daily worksheet then feeds the total sales values/number of orders etc back to a master record sheet in the workbook, if that makes sense. Please let me know if you need any more details/screenshots etc...very appreciative of any help...I have searched everywhere!!

 

[Peter_SSs]

Welcome to the MrExcel board!

each file is only used by the one sales person so only has the one set of prefixes/suffixes.
... repeating across the multiple sheets, for multiple people with different details.

The two red bits seem contradictory.

So further explanation and/or small screen shot(s) would help. (My signature block has suggestions re screen shots)

Do you expect the vba code (if I have understood correctly, code will be required) to insert the (current) date too or will the user enter that along with their prefix/suffix and the code just add the consecutive 2-digit number?

What version of Excel are you using?

 

[LauraB2013]

The two red bits seem contradictory.

So further explanation and/or small screen shot(s) would help. (My signature block has suggestions re screen shots)

Do you expect the vba code (if I have understood correctly, code will be required) to insert the (current) date too or will the user enter that along with their prefix/suffix and the code just add the consecutive 2-digit number?

What version of Excel are you using?

---

Thanks Peter...each sales person has a file...and each sales person has their own prefix/suffix...but within the one file (with multiple worksheets - one/day) only the one prefix/suffix combination would be used. Does that make sense!? If it is possible to have the date and a consecutive 2-digit number automatically fill in the 'reference' column in each day's sheet - that would be ideal....and then preferably also automatically insert the prefix/suffix as well...so the user doesn't have to enter anything! The date could be pulled from a cell on each worksheet - the date of the worksheet is at the top of each sheet.....I have Excel 2010

 

[Peter_SSs]

Thanks Peter...each sales person has a file...and each sales person has their own prefix/suffix...but within the one file (with multiple worksheets - one/day) only the one prefix/suffix combination would be used. Does that make sense!?

Yes, thanks

The date could be pulled from a cell on each worksheet - the date of the worksheet is at the top of each sheet

Where (what cell) exactly?

If it is possible to have the date and a consecutive 2-digit number automatically fill in the 'reference' column in each day's sheet

Where exactly? Which column? Starting in which row?

...and then preferably also automatically insert the prefix/suffix as well...so the user doesn't have to enter anything! ....

So how would I (the code) know what the prefix/suffix are?



Finally what would trigger all this to happen? And how many rows?

 

[LauraB2013]

The date is located in B1 of each sheet, but is entered from the master sheet so has a formula to calculate it (if that makes any difference) which is ='Monthly Total'!A3 . The reference column is from A3 to A36 on each sheet. The first users prefix is LR and suffix is SA. so the reference should look like this LR##ddmmyySA. An entry into the adjacent cell B3-B36 of the customer name should trigger a reference number to be generated. I trust that is all clear and thanks ever so much for your fast and friendly help

 

[Peter_SSs]

Perhaps you don't need vba code after all. See if this would work for you. Test in a copy of your workbook.

1. Select all the sheets in the workbook you want this to happen on.

2. Select A3 on the active sheet and enter this formula

="LR"&TEXT(ROWS(A$3:A3),"00")&TEXT(B$1,"ddmmyy")&"AB"

3. Drag the formula down to A36

4. While A3:A36 is still selected (& also multiple sheets still selected), change the Font colour of those formula cells to White.

5. Now ungroup the selected sheets. (Right click the active sheet tab and choose 'Ungroup Sheets')

6. Select the first of these sheets and select A3:A36 on that sheet

7. Home tab|Conditional Formatting|New Rule ...|Use a formula to determine which cells to format|Format values where this formula is true:| =B3<>"" |Format...|Font tab|Check that the 'Color:' drop-down says Automatic|OK|OK

At this point check that entering a value anywhere in B3:B36 on that sheet causes the Prefix/Number/Date/Suffix to appear in column A

8. Still on this sheet, ensure A3:A36 is selected

9. Double click the 'Format Painter' (Left end of Home ribbon tab)

10. Sequentially select each of the other relevant sheet tabs and then click on cell A3 only in that sheet

11. Once you have done step 10 for all the relevant sheets, click the Format Painter again to deactivate it

 

[LauraB2013]

Thank you so much! That has worked awesomely

 

[Peter_SSs]

Thank you so much! That has worked awesomely

Glad to have helped. Thanks for letting us know how it went.