Hi,
Overall I'm trying to use a filter function with data from a separate sheet. Problem is the data is not very consistent and the column headers move around. I can use the address and match to find my column letter in the other sheet but I want to be able to put it back in to an overall function like the one at the bottom. It's part of a large function but this is a step I'm stuck on. For reference say data is in Sheet1 and the column with header "Start time" is in column L.
Find "Start time" = Address(1,match("Start time",Sheet1!A1:Z1,0)),4)
Returns L1
If I just want L = SUBSTITUTE(ADDRESS(1,(match("Start time",Sheet1!A1:Z1,0)),4),"1","")
=ArrayFormula(iferror(small(filter(Sheet1!L:L, Sheet1!L:L>0, Sheet1!A:A=A1),1),""))
Sheet1!A:A=A1 does not change just looking up the same value to cross reference rows
My first thought was an indirect function but it doesn't seem to work. Any help is appreciated. I'll update if I find the correct formula or function.
Overall I'm trying to use a filter function with data from a separate sheet. Problem is the data is not very consistent and the column headers move around. I can use the address and match to find my column letter in the other sheet but I want to be able to put it back in to an overall function like the one at the bottom. It's part of a large function but this is a step I'm stuck on. For reference say data is in Sheet1 and the column with header "Start time" is in column L.
Find "Start time" = Address(1,match("Start time",Sheet1!A1:Z1,0)),4)
Returns L1
If I just want L = SUBSTITUTE(ADDRESS(1,(match("Start time",Sheet1!A1:Z1,0)),4),"1","")
=ArrayFormula(iferror(small(filter(Sheet1!L:L, Sheet1!L:L>0, Sheet1!A:A=A1),1),""))
Sheet1!A:A=A1 does not change just looking up the same value to cross reference rows
My first thought was an indirect function but it doesn't seem to work. Any help is appreciated. I'll update if I find the correct formula or function.
