I'm attempting to create a spreadsheet that tracks the progress of a sample going through a lab. I want to have the user enter in a piece of information (say the Sample ID) and return what phase that sample is in.
In the user interfacing sheet, they enter the Sample ID in cell B1
Using this formula "A" =MATCH(B1,progress!A1:A29,0), I can return the row number that their sample is in, returned in A6
Using this formula "B" =INDEX(progress!A1:H1,MATCH(1E+100,progress!A2:H2,1)), I can return the column header that has the cell to the farthest right which contains text in it (say we fill in dates as we go from left to right, farthest right being project=complete), returned in A5
What I would like to do is integrate the results of formula A into formula B such that the "A2:H2" is populated by the "2" that is returned from formula A
I've been trying things like this =INDEX(progress!A1:H1,MATCH(1E+100,progress!(INDIRECT("A"&A6&":"&"H"&A6)),1) but something in there is way wrong. Any advice?
[sheet attached, hopefully]
Bookmarks