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.

 

[Marcílio_Lobão]

defender, Good morning.

Try to use:

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

Please, tell us if it worked as desired.

I hope it helps.

 

[defender]

thanks marcilio for your reply. I tried with the coma between the two formulas as you suggested but it returns #value !. I also tried with + sign but the same outcome. anything else i can try?

 

[Marcílio_Lobão]

defender, Good afternoon.

I did a little example for you using my suggestion:
https://www.sendspace.com/file/1fvdoa

Take a look at it and tell us if it worked as you desired.

I hope it helps.

 

[defender]

Thank you that worked great. How can i make this formula work in sheet 2 so that the number/code sequence continues from the last one. This formula is in cell n8 to n27, and will be in this same range in sheet 2.

 

[Marcílio_Lobão]

defender, Great Christmas morning.

I'm sorry but I really don't understand the explanation.

What exactly will the formula have to do involving Tab 2?

Please, use my example and manually put the answer that you would like to see as a result.
Save it at www.sendspace.com and put a link to download here.

This will facilitate our response to you.

 

[defender]

Merry Christman Marcilio,

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

In sheet 2 i have put the expected result that i want to achieve. As you can see the last PSC-S- code in sheet 1 ended in 0005, therefore when i continue the formula i would like the the code sequence to also continue (this should also apply to PSC-L-). I hope this makes sense, and thank you for your time.

 

[defender]

Hi anyone able to help with this.

I am using this formula:

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

where D8 is a drop down list that does down to cell D27.

So each time i select S1, S2, S3 I will get a value PSC-S-0001, PSC-S-0002, PSC0003, likewise if i selected T1, T2 or T3 (PSC-L-0001 etc) and in theory this could continue to PSC-S-0027 if all the cell range is used. My problem lies that i want to use this same formula in sheet 2, but i want the value to continue from where it ended in sheet 1. i.e if in sheet 1 PSC-S-0016 was the last value, than in sheet 2 if i select either S1, S2, S3 than the value returned should be PSC-S-0017. I also need to apply this condition to T1, T2, T3.

If i was not using sheet 2 then i would not have a problem as i could just drag the formula down as far as i wanted.

Can anyone help with this please.

 

[Marcílio_Lobão]

defender, Good afternoon.

I made an example for you.
Take a look and tell us if the new formula works the way you want it.

https://www.sendspace.com/file/2anyct

But your column of expected result is very confusing.
Her logic is totally different from the logic of the Sheet1.

I hope it helps.

 

[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