Alphanumeric increment formula

[defender]

Hi all,

I'm having a problem repeating/adding this formula in the same cell with another condition.

=IF(OR(D8="S1", D8="S2", D8="S3"), "PSC-S-"&TEXT(ROWS($1:1),"0000"),"")

D8 is a drop down list with different items.

I want to plus the above formula with the one below in the same cell


=IF(OR(D8="T1", D8="T2", D8="T3"), "PSC-L-"&TEXT(ROWS($1:1),"0000"),"")

here you can see the alphanumeric code is slightly different(PSC-S, to PSC-L) if i want to choose these items from the drop down list.

I have also created multiple worksheets, and would like the increment to continue over the other worksheets

Can anyone please help with how i can combine or add these formulas.

Thank you.

 

[defender]

Hi Marcilio,

Thank you for your help, this worked just great.

I wanted to see if i could do this on sheet 3 also ,so in the formula you gave me:

=IF(OR(D8="S1", D8="S2", D8="S3"), "PSC-S-"&TEXT(ROWS($1:1)+MATCH(REPT("z",255),Sheet1!$D$8:$D$27),"0000"), IF(OR(D8="T1", D8="T2", D8="T3"), "PSC-L-"&TEXT(ROWS($1:1)+MATCH(REPT("z",255),Sheet1!$D$8:$D$27),"0000"),""))

i replaced sheet1! with sheet2! but this did not work. How i can i now make this work in sheet3?

Once again thank you for your help and patience.

-Defender

Hi, i just realised something else if its possible to do. As well as the above problem above, i've noticed that the formula is incrementing down the cell (see result in black). i.e

[TABLE="width: 323"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]S1[/TD]
[TD]PSC-S-0001[/TD]
[TD]PSC-S-0001[/TD]
[/TR]
[TR]
[TD]T1[/TD]
[TD]PSC-L-0002[/TD]
[TD]PSC-L-0001[/TD]
[/TR]
[TR]
[TD]S2[/TD]
[TD]PSC-S-0003[/TD]
[TD]PSC-S-0002[/TD]
[/TR]
[TR]
[TD]S3[/TD]
[TD]PSC-S-0004[/TD]
[TD]PSC-S-0003[/TD]
[/TR]
[TR]
[TD]S2[/TD]
[TD]PSC-S-0005[/TD]
[TD]PSC-S-0004[/TD]
[/TR]
[TR]
[TD]T3[/TD]
[TD]PSC-L-0006[/TD]
[TD]
PSC-L-0002


[/TD]
[/TR]
</tbody>[/TABLE]

The text in red is what i would like the result to be i.e If i select S1, S2 or S3 for the first time i get PSC-S-0001, then the second time it should be PCS-S-0002 even if the second time was way down in cell 15 (at the moment if the second time i selected either S1, S2 or S3 down in cell 15 it would give the result PSC-S-0015)

I've attached a link and in sheet 1 with text in red showing what results i expect.

https://www.sendspace.com/file/cnq5z4

Once again thank you and sorry for any confusion!