REF Error when using INDIRECT function to create dynamic row reference-CodePudding

I have a matrix grid in "MasterSheetGrid". I have separate sheets that divide this info into certain dimensions, making it easier to handle for the user.

In order to make the file dynamic, i am trying to use INDIRECT Function within a function, to locate which row of the MasterSheetGrid to look for the information before returning.

The formula works when i specify the row manually, but using INDIRECT i receive a REF error, even though nothing is deleted.

Manual Formula =INDEX(MasterSheetGrid!$5:$5,MATCH((XLOOKUP($J6,$5:$5,6:6)),MasterSheetGrid!6:6,0))

Formula to locate the row =(MATCH($C6,MasterSheetGrid!$C:$C,0))

Attempt to merge both using INDIRECT by referencing the cell where the above formula is stored, which results in REF INDEX(MasterSheetGrid!$5:$5,MATCH((XLOOKUP($J6,$5:$5,6:6)),(INDIRECT(J2:J2,0))))

Ideally i would like to not have to use a cell to store the lookup row formula in, but i thought if i could create a correct formula with the cell reference, i could repeat for the formula.

Does anyone know what i am doing wrong?

This is the view of the user. The formula would sit within column K

This is the MasterSheetGrid view

CodePudding user response：

To refer a range using INDIRECT you can use the following syntax (as a string delimited, for example delimited by "):

=INDIRECT("J2:J10")

for a cell, this works:

=INDIRECT(J2)

but if you try the same for a range:

=INDIRECT(J2:J10) -> #REF!

you get #REF!

If you are going to refer a Sheet from your Worksheet, then you need in all cases to enter the input argument as string:

=INDIRECT("Sheet1!A1:A10")
=INDIRECT("Sheet1!A1")

Note: By the way you are invoking INDIRECT using the second input argument (a1) which is optional with the value 0. It is not required for getting a referring a range as I showed before.

I suspect this is the issue you are having

CodePudding user response：

Instead of using INDIRECT which would cause recalculation at any change in the file, I recommend using INDEX instead. You refer to a fixed sheet name, therefore no need to use INDIRECT.

=INDEX(MasterSheetGrid!$5:$5,MATCH((XLOOKUP($J6,$5:$5,6:6)),INDEX(MasterSheetGrid!$1:$50,J2,),0))

Would be the equivalent of what you tried.

Proper use of INDIRECT would be:

=INDEX(MasterSheetGrid!$5:$5,MATCH((XLOOKUP($J6,$5:$5,6:6)),INDIRECT("MasterSheetGrid!"&J2&":"&J2),0))