0

Trying to use the INDIRECT formula to pull a summary at the end of a large workbook. I have listed the Worksheet names in a single tab and now wish to use these to pull specific cell data into a summary, ie invoice no, net sales, VAT, all of which are on identical cells on each sheet.

If I have the worksheet name in cell A3 in my summary tab and I wish to retrieve data within cell H34 of my specified sheet, I have used the following formula:

=INDIRECT("'"&$A$3&"'!H34",FALSE)

However I am only retrieving the #ref error.

Can anyone tell me what I am doing wrong?

Thanks, LA

Improve this question
5
  • Put ="'"&$A$3&"'!H34" in another cell - what does it say? Is there a sheet that has that EXACT name? What does your Excel expect for separating arguments to functions: , or ; ?
    – Hannu
    Oct 10, 2015 at 17:26
  • It returns the sheet name & the cell reference: ' Mr Smith'!H34 The sheet name should be correct as I used a formula to list the sheet names, and have just valued the contents....
    – Lesley-Ann Henry
    Oct 10, 2015 at 17:29
  • From your typing it seems there is a ` ` (space char) between ' and Mr.
    – Hannu
    Oct 10, 2015 at 17:35
  • If that fixed the problem, then make the tick-mark next to the correct answer be green (click on it). ;-)
    – Hannu
    Oct 10, 2015 at 17:40
  • Tough decision. There were two problems, and each answer fixes one. :-)
    – fixer1234
    Oct 10, 2015 at 20:53

2 Answers 2

Reset to default
1

The second argument to INDIRECT is whether the first argument is an A1 style reference (TRUE or omitted) or an R1C1 style reference (FALSE). By passing FALSE, you're saying the reference is an R1C1 style reference, so something like Sheet1!R8C34, but it's obviously not. Change the FALSE to TRUE (or just drop it altogether) and it should work.

Improve this answer
2
  • Thanks, so I've tried that and still not getting the cell value to return? Any other ideas?
    – Lesley-Ann Henry
    Oct 10, 2015 at 17:20
  • Are you sure you've got all the cell references right and the sheet name in A3 right? I created a worksheet with two sheets, Sheet1 and Sheet2. In Sheet2!H34 I put 42, in Sheet1!A3 I put Sheet2, and in Sheet1!A4 I put the INDIRECT. With FALSE I get #REF, with TRUE or nothing I get 42, and changing Sheet2!H34 changes Sheet1!A4 as expected.
    – blm
    Oct 10, 2015 at 17:37
1

If even a single space char differs in the text used as reference (content of "A3") and the actual Sheet name - then INDIRECT will give "#REF"

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.