• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

INDIRECT formula doesn't work [SOLVED]

B

brems

Member
Hello to all,


I'm using an INDIRECT formule to refer to a cell reference in another tab.

When the name of the tab is 1 word (example: table1), there is no problem at all. I get the specific cell content from the tab with name table1. The problem arises when the name consists of more then one word (example: sales table). Then the result of the formula is an error. (#ref)


=INDIRECT(cell ref&"!cell ref")


Renaming all the tab names to one name is a solution but the problem is that the sheet consists of more then 30 tabs and renaming them would be time consuming. Above that, the sheet is the result of an export from another program.

So I would rather be happy with finding the solution in adjusting the formula.


Thank you for your advice and your help.

regards

Wim
 
You need to surround the name with single quotation marks, something like so:

=INDIRECT("'" & B2 & "'!" & C2)
 
Hi Luke,


Thanks for your quick response to my question. I added an example file which you can find with the link below.


The strange thins is, that when I uploaded in Google Docs my formule worked perfectly but when I view it in Excel 2010 I get the #ref-error.


I didn't manage to apply your solution so I'm requesting your assistance again.


https://docs.google.com/spreadsheet/ccc?key=0Av-NhSp9ULywdHk4c3N4dDRudkRIWVFsUkxUN2lYQ2c&usp=sharing


Thanks

Wim
 
Hmm, I am not sure how Google Docs work. It may have a built in functionality to work around that. In general, most programs/code languages need some way for you to indicate that a two-word phrase refers to a single thing, but Google is pretty smart. =)


I'm afraid I can't access uploaded files; can you describe what went wrong when you tried the formula I posted?
 
Hi Wim ,


Your worksheet does not have the single quotes that Luke has put in , in his formula ; please recheck. These are required whenever the sheet name has one or more spaces in it.


Narayan
 
Hi Narayan,


That's correct. Like I wrote in my previous post, I wasn't able to apply Luke's solution. Somehow, with all the variations with the quotes I tried, Excel returned an error message.


Since my first reference (tab) isn't text I wasn't able to apply those quotes.

Probably, I'm doing something wrong... ;))


I know the problem is due to the spaces in the tab name but I don't know how to resolve it.


Many thanks for your support.


regards

Wim
 
Hi, Wim!

Try this formula in E6 of worksheet Lookup and copy down to E7. It just follows Luke M's indications:

=INDIRECTO("'"&C6&"'"&"!D11") -----> in english: =INDIRECT("'"&C6&"'"&"!D11")

Regards!
 
Hi SirJB7, Luke and Narayan,


Thanks for your assistance. The trick with the single and the double quotes worked perfect.


This is the result and it permitted me to fetch a lot of data in a very quick way from all the different tabs in my workbook.


=INDEX(INDIRECT("'"&D4&"'"&"!$A$2:$AH$14");MATCH($E$2;INDIRECT("'"&D4&"'"&"!$A$2:$A$14");0);MATCH(samenvatting!$E$3;INDIRECT("'"&D4&"'"&"!$A$2:$AH$2");0))


regards

Wim
 
You must log in or register to reply here.
Back
Top