I am running an Indirect function to look through different tabs and return values from specific cells, for example:
=IFERROR(INDEX(INDIRECT(J13&$P$6),MATCH(J13,INDIRECT(J13&$P$5),0)),"n/a")
J13 is the tab name // P6(!Q80 in this case) is the cell where the data is // P5(!J5 in this case) is the cell in which J13 will match
However, if J13 is 1 and the Tab name is 1 and !J5 is 1, all is good, value is returned, however, if J13 is 11+12, Tab name is 11+12 and !J5 is 11+12, it returns #REF (n/a in my case since I have an IFERROR)
(https://i.stack.imgur.com/jvnWO.png)
How may I fix this?
Changed the cell format, used & instead of +, nothing works. I looked through other posts but they answer other issues.
>Solution :
When you have a space in the worksheet name, it requires the name to be padded in single quotes. When using INDIRECT
, it is best to pad the sheet names with single quotes always. This will work even with sheets which do not have space in their names.
Example
=INDIRECT("'"&D3&"'"&E3)
and
In your case, INDIRECT(J13&$P$6)
becomes INDIRECT("'"&J13&"'"&$P$6)
. Similarly INDIRECT(J13&$P$5)
becomes INDIRECT("'"&J13&"'"&$P$5)
.