Hi,
First post, but irregular visitor - thanks for previous help!
I have someone else's spreadsheet that tries to simulate a sort of gantt chart. However, it doesn't explicitly give the key dates of events, so I'd like to extract them into a column.
Imagine the sheet has dates in a row b3:bz3.
a4:a300 has a list of locations.
The 'content' b4:bz300 is populated by formulas and can be: zero, blank, text or numeric.
So for example, the row for location 'New York' could have:
{0,0,0,0,0,0,0,0, ,0,0,0,text,0,text,0,0,12,400,400,0,0,0,0,text,.....}
I'm only interested the column the first non-zero number appears in. In the example, this would be "12", in the 18th col.
The formula I have so far:
=INDEX($B$3:$BZ$300,1,MATCH(TRUE,INDEX($B4:$BZ4>0,0,0),0))
I think it's close... but it is returning the first text cell, not the first numeric one.
Any suggestions?
As said, this is someone else's (protected) spreadsheet which is updated every couple of weeks. I need to extract this date data for my part of the project, but I'll probably try encourage the owner to include the column in future.
Thanks in advance.
First post, but irregular visitor - thanks for previous help!
I have someone else's spreadsheet that tries to simulate a sort of gantt chart. However, it doesn't explicitly give the key dates of events, so I'd like to extract them into a column.
Imagine the sheet has dates in a row b3:bz3.
a4:a300 has a list of locations.
The 'content' b4:bz300 is populated by formulas and can be: zero, blank, text or numeric.
So for example, the row for location 'New York' could have:
{0,0,0,0,0,0,0,0, ,0,0,0,text,0,text,0,0,12,400,400,0,0,0,0,text,.....}
I'm only interested the column the first non-zero number appears in. In the example, this would be "12", in the 18th col.
The formula I have so far:
=INDEX($B$3:$BZ$300,1,MATCH(TRUE,INDEX($B4:$BZ4>0,0,0),0))
I think it's close... but it is returning the first text cell, not the first numeric one.
Any suggestions?
As said, this is someone else's (protected) spreadsheet which is updated every couple of weeks. I need to extract this date data for my part of the project, but I'll probably try encourage the owner to include the column in future.
Thanks in advance.