Please see attached.

I'm looking for a formula I can fill-down in Column-A that will allow me to auto-number Column-A based upon values in Column-B. If Column-B contains the word "Section" then the corresponding row in A is blank (or could be "Section #", if that is easier). However, if Column-B does not contain the word "Section" then I need to:
- Prefix the value in A with "REQ-"
- then follow it with the section number
- then follow it with a three digit auto-number value

For a simple example:
- B1 equals "Section 1 - more text here".
- B2 equals "some text".
- B3 equals "more text".
- B4 equals "Section 2 - with more text".
- B5 equals "yet more text".
- A1 would show "" (or it could show "Section 1").
- A2 would show "REQ-1001".
- A3 would show "REQ-1002".
- A4 would show "" (or it could show "Section 2").
- A5 would show "REQ-2001"

I can do it using a couple of cheater columns, but I'd really like to do it just using Column-A. Also, what I'm doing in the attached example seems to be way more complicated than needed.

Any guidance or example solutions will be appreciated.

Thanks in advance,
Andrew

Attached Files

Auto-Number.xlsx (11.2 KB, 12 views)