Can you please help with an INDIRECT formula error?

Try

=INDEX(INDIRECT($A14&"!A2:A101"),MATCH("Comments",INDIRECT($A14&"!A1:A100"),0))

Originally Posted by jason.b75

Try

=INDEX(INDIRECT($A14&"!A2:A101"),MATCH("Comments",INDIRECT($A14&"!A1:A100"),0))

jason.b75, you are brilliant, thank you so much.

Originally Posted by protonLeah

=OFFSET(A1,MATCH("Comments",INDIRECT(A14 & "!" & "A1:A100"),0),1)
you can't have a height of zero

Ok, I appreciate your help, but it does seem to work. I just pasted it in and it worked correctly.

Can I ask another question though:

It works for all the tabs that have numbers as names, but it gives a #REF! error when the tab has a name with letters in it. For example, one of the tabs is named "IC Debtors".

Thanks again for the responses.

Originally Posted by Stephen01

I just pasted it in and it worked correctly.

Does it work correctly, or does it just appear to work correctly?

That will fetch the result from the sheet holding the formula, not the sheet referred to in A14.

For example, one of the tabs is named "IC Debtors".

When the tab name contains spaces then it needs a different approach, this will still work with other tab names.

=INDEX(INDIRECT("'"&$A14&"'!A2:A101"),MATCH("Comments",INDIRECT("'"&$A14&"'!A1:A100"),0))

This method is also required for numeric tab names, for some reason excel can see the error and correct those, but not the tab names with spaces.

Originally Posted by jason.b75

Does it work correctly, or does it just appear to work correctly?

That will fetch the result from the sheet holding the formula, not the sheet referred to in A14.



When the tab name contains spaces then it needs a different approach, this will still work with other tab names.

=INDEX(INDIRECT("'"&$A14&"'!A2:A101"),MATCH("Comments",INDIRECT("'"&$A14&"'!A1:A100"),0))

This method is also required for numeric tab names, for some reason excel can see the error and correct those, but not the tab names with spaces.

Thanks once again jason.b75, problem solved. This will save a lot of time for me and others.

@ Stephen01. Welcome to the forum.

If you are satisfied with the solution(s) provided, please remember to mark your thread as Solved.

New quick method:
Select Thread Tools (above first post on page) -> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
Go to the first post
Click edit
Click Go Advanced
Just below the word "Title" you will see a dropdown with the words "No prefix".
Change to "Solved"
Click Save

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Thanks Cutter, I've marked the thread as solved now. I have given rep to both posters that helped me.