Sequential ID numbers generated for non-blank fields

[exceler_52]

I've done a lot of looking around and found some formulas and scripts that are close to what I need, but nothing close enough that I can mold it to my needs. I have a list of points (column C) with a column on each side indicating whether the point is an input (column B) and/or an output (column D). I need something that will generate strings in columns A & E that begin with a text prefix followed by an incremented four digit sequential ID and skips blanks like the below example. Any suggestions?

example.jpg

[djapigo]

Hi exceler,

Try this in A2: =F(B2="","","$INPUT"&TEXT(COUNTIF($B$2:B2,"Y"),"0000"))
and in D2: =F(D="","","$INPUT"&TEXT(COUNTIF($D2:D,"Y"),"0000"))

Let me know if this doesn't work... or if you want to understand how it works...

Dennis

[exceler_52]

B-E-A-UTIFUL! I was worried it was going to require a VB script. With a few minor corrections of:

A2=IF(B2="","","$INPUT"&TEXT(COUNTIF($B$2:B2,"Y"),"0000"))
E2=IF(D2="","","$OUTPUT"&TEXT(COUNTIF($D$2:D2,"Y"),"0000"))

that is working exactly how I need it to. Many thanks!!!

[djapigo]

I'm glad you figured out that I mean IF statements... nice work... and I'm glad it worked!

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save