1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Ask an Excel Question' started by TheJacobsDad, Oct 10, 2018.

  1. TheJacobsDad

    TheJacobsDad New Member

    Messages:
    2
    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
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    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.
  3. TheJacobsDad

    TheJacobsDad New Member

    Messages:
    2
    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

Share This Page