Forum Discussion
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 name manager section in formulas, have set drop down lists for each column and used the formula =INDIRECT(I18) INDIRECT(J18)
It is only affecting 2 rows from the matrix but other than that it works
Anyone got any tips?
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”.
2 Replies
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”.
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
