+ Reply to Thread
Results 1 to 6 of 6

Sheet Reference from Cell

  1. #1
    Registered User
    Join Date
    04-07-2007
    Posts
    16

    Sheet Reference from Cell

    Hi All I hope you can help.

    Attached is a screenshot which I hope will explain it.

    The formula I have is =SUM('1:31'!D24)

    What I need to be able to do is to have the sheet names come from entries in cells.

    ie. =SUM('B20:D20'!D24)

    So if someone types 3 in B20 and 10 in D20 it will sum cells D24 from sheets 3 to 10.


    How can I do this?
    Attached Images Attached Images

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Hi,

    This seems to work

    =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(B20&":"&D20))&"'!$D$24"),">0",INDIRECT("'Sheet"&ROW(INDIRECT(B20&":"&D20))&"'!$D$24")))

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Registered User
    Join Date
    04-07-2007
    Posts
    16
    When I put in
    =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(B 20&":"&D20))&"'!$D$24"),">0",INDIRECT("'Sheet"&ROW (INDIRECT(B20&":"&D20))&"'!$D$24")))

    It gives me an error. #REF!

  4. #4
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    See if removing the space helps between B and 20

    VBA Noob

  5. #5
    Registered User
    Join Date
    04-07-2007
    Posts
    16
    Tried that, It didn't work.

    I've attached the speadsheet. Sheets 1 and 2 have enough data in them so it should be working.

    Thanks for your help BTW

  6. #6
    Registered User
    Join Date
    04-07-2007
    Posts
    16
    Thanks for all the help, someone found it for me.

    =SUM(N(INDIRECT(ROW(INDIRECT(B20&":"&D20))&"!D24")))

    with CONTROL+SHIFT+ENTER and it works

+ 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