I have two worksheets in my workbook. First one is full of data (headings in row 1, each row from 2 downwards is a new record); second one pulls out certain records based on specified criteria.
In Sheet2:
Because the column headings change (relative references are not an option in this case), I'm using MATCH to identify the column in which to search where $D$1 is the heading I want it to look for:
MATCH($D$1,Data!$1:$1,0) -----> Returns column of required heading.
And then adding to that the row
ROW() -----> Returns row that the formula is in - the row numbers on each worksheet are consistent i.e. I want the formulas in Sheet2! row 4 to search in Data! row 4.
So piecing them together I'm using
INDIRECT("Data!"&ADDRESS(ROW(),MATCH($D$1,QryAll!$1:$1,0)))
to return the contents of the same row in the searched column in the first tab.
This part works.
Then I wanted to add a conditional element and tried this:
{=IF(INDIRECT("Data!"&ADDRESS(ROW(),MATCH($D$1,Data!$1:$1,0)))=X15:X17,TRUE,FALSE)}
E.g. say the column I search for using the MATCH is 'vehicle type liked', and the INDIRECT above returns 'motorcycle'. I'm trying to then search in X15:X17 for if the person likes one of the vehicles in the "acceptable list" - either a motorcycle, van or truck (those three entered in X15:X17).
When I enter the formula above and enter as an array, I get #Value!.
If I modify the formula to refer directly to the cell instead of using INDIRECT, it works
e.g {=IF(Data!C3=X15:X17,TRUE,FALSE)}; with "motorcycle" in Data!C3, it returns "TRUE".
When I put in the INDIRECT (which when evaluated on its own returns "Data!C3"), it doesn't work.
It seems that the formula falls over in the use of the INDIRECT but not otherwise in the sequence/construction of the formula - how can I make the IF in the array accept the INDIRECT?
Regards and thanks in advance,
Ben
In Sheet2:
Because the column headings change (relative references are not an option in this case), I'm using MATCH to identify the column in which to search where $D$1 is the heading I want it to look for:
MATCH($D$1,Data!$1:$1,0) -----> Returns column of required heading.
And then adding to that the row
ROW() -----> Returns row that the formula is in - the row numbers on each worksheet are consistent i.e. I want the formulas in Sheet2! row 4 to search in Data! row 4.
So piecing them together I'm using
INDIRECT("Data!"&ADDRESS(ROW(),MATCH($D$1,QryAll!$1:$1,0)))
to return the contents of the same row in the searched column in the first tab.
This part works.
Then I wanted to add a conditional element and tried this:
{=IF(INDIRECT("Data!"&ADDRESS(ROW(),MATCH($D$1,Data!$1:$1,0)))=X15:X17,TRUE,FALSE)}
E.g. say the column I search for using the MATCH is 'vehicle type liked', and the INDIRECT above returns 'motorcycle'. I'm trying to then search in X15:X17 for if the person likes one of the vehicles in the "acceptable list" - either a motorcycle, van or truck (those three entered in X15:X17).
When I enter the formula above and enter as an array, I get #Value!.
If I modify the formula to refer directly to the cell instead of using INDIRECT, it works
e.g {=IF(Data!C3=X15:X17,TRUE,FALSE)}; with "motorcycle" in Data!C3, it returns "TRUE".
When I put in the INDIRECT (which when evaluated on its own returns "Data!C3"), it doesn't work.
It seems that the formula falls over in the use of the INDIRECT but not otherwise in the sequence/construction of the formula - how can I make the IF in the array accept the INDIRECT?
Regards and thanks in advance,
Ben