Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    Wilmington, North Carolina, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Consolidation Using Indirect() (XP)

    Hi all - as a new member, I apologize in advance if this falls under "asked and answered"

    I have tried unsuccessfully to use the indirect function to consolidate sheets which have month names. When I try to build a formula using cell contents to reference the beginning and ending sheets, I cannot get a real answer. If I use indirect to reference a single sheet all is well. Is there a workaround? This would be helpful in allowing the uses of dropdown boxes to allow user selection of beginning and end months for dynamic consolidation of the information in the spreadsheet.

    Thanks in advance (crossing fingers)

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consolidation Using Indirect() (XP)

    Your question is a bit general, but it sounds like what you want to do is possible. Could you give is an example, preferably upload a workbook with dummy data, that show what you are trying to do?
    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Consolidation Using Indirect() (XP)

    Eric, there is a problem in that INDIRECT does not handle multisheet references. For instance:

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center valign=bottom>1</td><td valign=bottom>Sheet</td><td valign=bottom>' 2</td><td valign=bottom>' 3</td><td align=center valign=bottom>2</td><td valign=bottom>=SUM(INDIRECT(B2))</td><td valign=bottom>="'"&A1&B1&"'!"&"A1:A20"</td><td valign=bottom>returns valid result</td><td align=center valign=bottom>3</td><td valign=bottom>=SUM(INDIRECT(B3))</td><td valign=bottom>="'"&A1&B1&"'!"&"A1"</td><td valign=bottom>returns valid result</td><td align=center valign=bottom>4</td><td valign=bottom>=SUM(INDIRECT(B4))</td><td valign=bottom>="'"&A1&B1&":"&A1&C1&"'!"&"A1:A20"</td><td valign=bottom>returns #REF</td></table>
    See more in <post#=38848>post 38848</post#>.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    New Lounger
    Join Date
    Jan 2003
    Location
    Wilmington, North Carolina, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consolidation Using Indirect() (XP)

    Thanks I was afraid I remembered a problem of that nature. At least now I can stop banging into that brickwall.. Will try another approach.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Consolidation Using Indirect() (XP)

    Eric, have you looked at the Data, Consolidation function? Also the attachment to <!post=this post,151820>this post<!/post> may be of use.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •