Excel: Next incidence in row 1

[jonsi]

I have a fleet service scheduler on a spreadsheet that resembles a year-planner. To get it to do what I want I'd have to have a vast number of cells each containing IF statements which is stupid to say the least and unwieldy at best.

Scheduled vehicle services are marked in date headed columns with the letter "S" entered into the cell, once completed this is manually changed to a "C" leaving the remaining "S" cells alone.

Vehicle 12/07 13/07 14/07
ABC 123 C C S
DEF 456 C S S
XYZ 999 S S S

Using a straightforward LOOKUP I can always return the Cell address (or an OFFSET based on it) of the last incidence of "C" in a row, but I cannot seem to find a way of returning the Cell address of the next (ie first) incidence of "S" in the row.
eg within the range above I should return D2 for Row B22, C3 for B33 and B4 for B44. If I changed B4 to "C" the result should then return C4.

I'm sure I'm missing something really basic but I'm going round in circles with this one (could be the weather here ATM). Can someone help point me in the right direction please ?

thanks
Jonsi

 

[xlbo]

Jonsi - have a look at the MATCH function within each row

=MATCH("S",B22,0)

will return "3" as it is in the 3rd column of the data set

=MATCH("S",A22,0)

will return "4"

Use this and the ADDRESS function to create the cell ref

For row 2, I would use

=ADDRESS(ROW(),MATCH("S",A11,0),1,1)

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[jonsi]

Thanks Geoff,

As usual ...100% correct

Had completely forgotten about the MATCH Function. This works a treat and gives me exactly what I needed.