+ Reply to Thread
Results 1 to 8 of 8

SUM formula - change range with value input

  1. #1
    Registered User
    Join Date
    01-28-2010
    Location
    Sweden
    MS-Off Ver
    Excel 2007
    Posts
    20

    Lightbulb SUM formula - change range with value input

    Can someone help me to figure out how I should right below formula?

    "Smarter" version of a regular SUM(A1:A10) - I want too be able to input the value 10 (then 11 and 12 etc) in the range.

    =SUM(B7:INDIRECT("B"&AD4)) - if i copy this the "B" obviously do not change.
    =SUM(C7:INDIRECT("C"&AD4))
    =SUM(D7:INDIRECT("D"&AD4))
    =SUM(E7:INDIRECT("E"&AD4))
    etc
    etc
    ....
    ....

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,514

    Re: SUM formula - change range with value input

    I cannot visualise what you want. Please SHOW us on a sample Excel sheet. The yellow banner (top) tells you how to attach sample files.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  3. #3
    Registered User
    Join Date
    01-28-2010
    Location
    Sweden
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: SUM formula - change range with value input

    Hi Glenn,

    Apologise, the logic in above text wasnt there. Hence it is hard to visualise. Please see attached excel and image.

    Attachment 811551

    Attachment 811552
    Attached Images Attached Images
    Attached Files Attached Files

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    45,004

    Re: SUM formula - change range with value input

    One way:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    45,004

    Re: SUM formula - change range with value input

    And:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    copied down

  6. #6
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    3,160

    Re: SUM formula - change range with value input

    Try,
    H2=SUM(INDIRECT(CELL("address",A2)&":"&G2)), copy down
    F8=SUM(INDIRECT(CELL("address",A8)&":"&$F$7&ROW(A8))), copy down

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    45,004

    Re: SUM formula - change range with value input

    Or maybe
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    copied down

  8. #8
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    3,160

    Re: SUM formula - change range with value input

    H2=SUM(A2:INDIRECT(G2)), per TMS
    F8=SUM(A8:INDIRECT($F$7&ROW(A8)))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Countifs range should change dynamically with user input
    By abuharvey in forum Excel General
    Replies: 5
    Last Post: 04-10-2020, 03:50 AM
  2. [SOLVED] Worksheet Change? Use a formula cell as input and then revert back to original formula...
    By trickyricky in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-01-2016, 11:39 PM
  3. Defining an extra range to execute a different input change
    By ScottyDo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-07-2012, 02:19 PM
  4. [SOLVED] Change event to Add or update comment with input box when cell in a range is changed
    By fratello in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-29-2012, 06:40 AM
  5. Change input range of 50+ drop downs
    By Kazstankgardner in forum Excel General
    Replies: 3
    Last Post: 06-19-2012, 07:54 AM
  6. Replies: 2
    Last Post: 06-11-2012, 07:30 PM
  7. Custom change Macro's Range value, from cells input.
    By Kenji in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-17-2009, 11:54 PM

Tags for this Thread

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