Forum Discussion

Frostyboy86's avatar
Frostyboy86
Copper Contributor
Jun 21, 2025
Solved

Issue with INDIRECT formula

Hi Guys i am having an issue with running a Indirect formula to take intersect a column and a row of a "skill vs will" matrix to give an intersecting answer. I have created from a selection in the n...
excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jun 23, 2025

    Your errors occur only when the reference in the INDIRECT function contains a space. Named ranges may not have spaces or other special characters in them. Excel replaced them with an underscore.

    So, in your case, J18 contains “Generally Willing”. Change that to “Generally_Willing” or use SUBSTITUTE within INDIRECT. Something like:

    =INDIRECT(SUBSTITUTE(J18, “ “, “_”))

    Similar for “Highly Driven”.

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jun 23, 2025

Your errors occur only when the reference in the INDIRECT function contains a space. Named ranges may not have spaces or other special characters in them. Excel replaced them with an underscore.

So, in your case, J18 contains “Generally Willing”. Change that to “Generally_Willing” or use SUBSTITUTE within INDIRECT. Something like:

=INDIRECT(SUBSTITUTE(J18, “ “, “_”))

Similar for “Highly Driven”.

  • Frostyboy86's avatar
    Frostyboy86
    Copper Contributor
    Jun 24, 2025

    Hi Riny, thank you for your help you are absolutely right, excel wasn’t happy with the space.

    i added in the underscore and this resolved the issue immediately.

    Thank you for taking the time to help me out

    Regards James 

Resources