Hi all,
I was first using the 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. I then started to use the formula below in sheet 2 so that the last result from sheet1 would be carried over.
=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"),""))
When i try to use this formula in sheet3, by replacing sheet1! with sheet2! this does not work?
Also, my formula is incrementing down the cell (see result in black). i.e
S1 PSC-S-0001 PSC-S-0001
T1 PSC-L-0002 PSC-L-0001
S2 PSC-S-0003 PSC-S-0002
S3 PSC-S-0004 PSC-S-0003
S2 PSC-S-0005 PSC-S-0004
T3 PSC-L-0006 PSC-L-0002
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)
The incrementing for an S code should be independant to an L code. How can i achieve this as well making this work in another sheet by looking at the last S code number and or L code number.
I attached a link, which will give a brief example of the what i am trying to achieve (expected result column) and what is happening at the moment.
https://www.sendspace.com/file/yojtr1
Thank you in advance for your kind help.
Bookmarks