Prefixed Numerical List

You can use:

=IFERROR(INDEX($Q$2:$Q$5,1+INT((ROWS(A$2:A2)-1)/99))&TEXT(1+MOD((ROWS(A$2:A2)-1),99),"00"),"")


for simplicity (and checking) I went to 99. Change the bits in RED to suit your needs.

The bit in Cyan... 00 for 99, 000 for 999, 0000 for 9999

I think this might be what you are looking for. Unfortunately it utilizes the SEQUENCE() function, so i am hoping you forgot to update your MS office version on your profile. This will only work for MS office 365.
In column A the person enters A1 or A2 or B1 or D55 ...etc etc.
In B1 use this array function. Make sure to enter with CTRL + SHIFT + ENTER so that the formula does not SPILL. If done so correctly { } will be added around your formula.

Formula:

Please Login or Register  to view this content.

Sorry, I have no idea what you want. Unless you can SHOW me on a sample Excel sheet, I'll step aside at this point.

The formula dosydos provided will work for what I was needing. I did provide an excel showing a little bit more as to what I would need. The issue could be that the amount of fields I will need to have available in column A will be close to 100 items. They would each have needed their own numbers pool to pull from. Lets say as if column A was departments and column B was projects in that department. It does look like I will be able to tweak the provided formulas and get it from there. You both have been very helpful.

Thanks,

Glad it worked and thanks for the rep!