Stumped

%3CLINGO-SUB%20id%3D%22lingo-sub-2013335%22%20slang%3D%22en-US%22%3EStumped%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2013335%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20you%20auto%20populate%20when%20it%20is%20not%20a%20fully%20numerical%20number%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA-01-01%3C%2FP%3E%3CP%3EA-02-01%3C%2FP%3E%3CP%3EA-03-01%3C%2FP%3E%3CP%3EA-04-01%3C%2FP%3E%3CP%3E...to%20A-38-01%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20appreciate%20any%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2013335%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2013385%22%20slang%3D%22en-US%22%3ERe%3A%20Stumped%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2013385%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F910581%22%20target%3D%22_blank%22%3E%40doncornerstone%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20case%20you%20can%20combine%20ROW%20and%20COLUMN%20functions%20to%20convert%20its%20placement%20to%20a%20number%20while%20using%20CONCATENATE%20to%20tie%20them%20together.%20I%20added%20a%20letter%20next%20to%20the%20corresponding%20table%20to%20make%20the%20lookup%20more%20dynamic%2C%20you%20can%20hide%20it%20by%20converting%20the%20font%20to%20White.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

How do you auto populate when it is not a fully numerical number?

 

A-01-01

A-02-01

A-03-01

A-04-01

...to A-38-01

 

Would appreciate any help.

4 Replies

@doncornerstone 

In this case you can combine ROW and COLUMN functions to convert its placement to a number while using CONCATENATE to tie them together. I added a letter next to the corresponding table to make the lookup more dynamic, you can hide it by converting the font to White.

@doncornerstone 

Depends on which version of Excel you are. Variants

="A-" & TEXT(ROW()-ROW($B$2),"00")&"-"&TEXT(COLUMN()-COLUMN($B$2)+1,"00")

or

="B-"&TEXT(INT((SEQUENCE(38,5)-1)/5)+1,"00")&"-"&TEXT(MOD(SEQUENCE(38,5)-1,5)+1,"00")

Appreciate the help, I'll look into this after the holiday.

Thanks again!

@Sergei Baklan 

 

Appreciate the help, I'll look into this after the holiday.

Thanks again!