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).