Dear Experts,
Please find here attached a workbook sample, In Dashboard worksheet at cell D7 i am trying to use indirect formula to fetch a text based on worksheet name but it is throwing #Ref. error. Current Excel Ver. is 2016.
Request to you please do help me out. Your precious support would highly be appreciated.
Many thanks and kind regards,
Neilesh
This:
=INDIRECT(B7&" C "&"!C7")
resolves to this:
=Vserver15(Prod.) C !C7
That sheet does not exist, hence the error.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Hi Ali,
I have tried =INDIRECT(B7&"!C7") as well in Dashboard worksheet at cell D7 and this one also throwing the same error "#Ref." and error i gets only when i do use the special characters in the worksheet name but when i do remove the special characters the formula works perfectly.
Is it possible keep the worksheet name as it is while using the indirect formula for the required output.
Please do help me out.
Last edited by Neilesh Kumar; 12-19-2017 at 10:17 AM. Reason: Completing the info
Try this in D7:
=INDIRECT("'"&B7&"'!C7")
Hi Expert,
The same "#Ref." error i am getting with the provided formula and the reason is that in the worksheet name i have used open and closed brackets as well in some of the worksheets i have used - as well and resulting i am getting error.
Is it possible to get the Indirect formula where i can keep the worksheets name with special characters.
Is this what you meant, as I have assumed that htere is meant to be a value in dashboard C7...
=INDIRECT("'"&B7&"'!C"&C7)
if not, what is meant to be in that cell (if relevant!!)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
When I copy and paste the formula from post #4 into cell D7 of the 'Dashboard' sheet, the result of the formula is "Production".
I'm not sure where the disconnect is here.
Dear Experts,
I have used the provided formula =INDIRECT("'"&B7&"'!C7") in Cell D7 by the 63falcondude Expert in Dashboard worksheet but due to the other worksheet name contains special character in my case it is throwing error. So, i am expecting the indirect formula where i need not to change the worksheet name.
Please find here attached workbook where i have used the provided formula =INDIRECT("'"&B7&"'!C7") formula in D7 and the same is throwing "#Ref."error.
Last edited by Neilesh Kumar; 12-19-2017 at 12:30 PM. Reason: Completing the info
The IP address Vserver15_Prod. is NOT the same as the sheet name !!!!, which is : Vserver15(Prod.)
Change one of them to reflect the other
Perfect catch Sir, Thank you so much 63falcondude and Glenn. Thank you once again Sir.
Regards,
Neilesh
Glad we could help. Thanks for the rep!
Likewise... Spotting the error was far from quick, though!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks