• 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.

Help with an INDIRECT Formula

TheJacobsDad

New Member
Hi everyone,

I hope someone might be able to help me out.

I have a workbook with several sheets detailing project info and one summary sheet.

On the summary sheet in cell A10 I type the project number (Which is the same as the corresponding sheet name) and when I have typed in the project number I want my formula to sum the the cells referenced on that sheet and display on my summary

So for example in the summary sheet in Cell A10 I type 45566 (Project Number) the formula will then reference this sheet using the indirect function and return me the cell values

I am trying this
=SUM(INDIRECT("'"&A10&"!"&"AN$6:AN$7",(INDIRECT("'"&A10&"!"&"AN$23",(INDIRECT("'"&A10&"!"&"AN$26:AN$29",(INDIRECT("'"&A10&"!"&"AN$31",(INDIRECT("'"&A10&"!"&"AN$33"))))))))))

And when I evaluate it appears to determine the sheet name correctly but I still get #REF
 

Chihiro

Excel Ninja
You are missing closing single quote from the looks of it.

"'"&A10&"!"&"AN$6:AN$7"

Should be...
"'"&A10&"'!"&"AN$6:AN$7"

Or....
"'"&A10&"'!AN$6:AN$7"

As well, each Indirect should be closed off, and not nested within another Indirect.
 

TheJacobsDad

New Member
That's great Chihiro the single quotation was the key.....Now here another question on the same formula.
I need to use this in multiple columns but I can't just copy/paste as the Cell references don't update as they are contained within Quotation marks.
Anyway I can easily do this without having to remove the quotation marks, and replacing them post paste
 
Top