+ Reply to Thread
Results 1 to 10 of 10

Can I use this situation?

  1. #1
    Registered User
    Join Date
    11-15-2006
    Posts
    9

    Can I use this situation?

    Is this possible?

    A1 = "B2"
    A2 = "B150"

    A3 = "SUM(A1:A2)" - referencing the actual values of those cells?

    Please help!

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Try

    =SUM(INDIRECT(A2):INDIRECT(A3))

    rylo

  3. #3
    Registered User
    Join Date
    11-15-2006
    Posts
    9
    Hey thanks for the reply, but that results in a "#REF" error. Any other suggestions?

  4. #4
    Forum Contributor
    Join Date
    03-14-2006
    Location
    Pakistan
    Posts
    1,791

    Lightbulb

    Quote Originally Posted by tonyrice
    Hey thanks for the reply, but that results in a "#REF" error. Any other suggestions?
    Hi
    try this
    =SUM(INDIRECT(A1),INDIRECT(A2))

  5. #5
    Registered User
    Join Date
    11-15-2006
    Posts
    9
    I appreciate the knowledgeable response. I think this one is a little trickier, but it's the real problem.....

    A1='Build Request Time Sheet'!A2
    A2='Build Request Time Sheet'!A100

    A3=SUM(INDIRECT(A1):INDIRECT(A2))

    This does not work. Any help?

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Hello tony, I thought this would work

    =SUM(INDIRECT(A1&":"&A2))

    but when I tested it didn't......although it will work if A1 remains the same but A2 is just

    A100

    does that help?

  7. #7
    Registered User
    Join Date
    11-15-2006
    Posts
    9
    what does the

    &

    take place of in the formula?

  8. #8
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    The & concatenates

    so

    If A1 contains just A5 and A2 just A10

    =INDIRECT(A1&":"&A2)

    concatenates A1 with ":" with A2 giving you

    A5:A10

    This works OK with simple cell refs but the 2 sheet names seemed to cause a problem.....

  9. #9
    Registered User
    Join Date
    11-15-2006
    Posts
    9
    Thanks for your help!

    But, its not working when you reference a cell in a different sheet using indirect when summing numbers. I wonder if there is a way.....

  10. #10
    Registered User
    Join Date
    01-22-2007
    Posts
    1

    You try this tony!

    Hello Tonyrice!

    I've checked with the following and works well. You can also try and find out.
    in A1: Sheet2!B2
    in A2: Sheet2!B150

    in Sheet2!B2:25
    in Sheet2!B15:250

    and entered in Sheet1!A3:SUM(INDIRECT(A1):INDIRECT(A2)

    I got in Sheet1!A3:275 !!!

    Thanks for your posting & Regards.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1