Linking the value from a scroll bar / spin button to a formula

    I have a set of numbers from rows 1 to 5 in column A. Let's say the set is:


    1

    2

    3

    4

    5


    =SUM(A1:A5) equals to 15


    I'd like to create a SUM formula for the values in those rows dependent on the value of a scroll bar or a spin button, e.g. if the value of the scroll bar (or a spin button - I don't know which one would work in this case) is 1, the SUM would be from row 1 to 5 (ie 15). If the value of the scroll bar is 3, the SUM should be from 3 to 5 (ie 12).


    How do I link the value from the scroll bar / spin button to the SUM formula?


    Thanks.

    I think I figured it out. I can use the OFFSET function.


    =SUM(OFFSET(A1,B7,0):A5) where B7 is the cell where the value from the scroll bar is.


    If there are any neater resolutions I'm happy to learn about them.


    Thanks!

    Just as an alternative, you could use:


    =SUM(INDEX(A1:A5,B7):A5)


    which is less volatile (not that it will make a lot of difference with a simple formula like that).

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Love 1

    Depends how you want to be able to use it. A scroll bar is easier if you want the option to make big changes, or have a visual indication of your current selection vs the max.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Love 1

    rory,

    I have another query related to this question .This one I couldn't figure out myself so I'm asking for your help.


    Let's say the data is as follows:

    - I have data in columns A, B, C, rows 1 to 10.

    - data contains numbers from 1 to 5.

    - in cell E1, I have a number 5.


    Now I want the formula to calculate the amount of times that number (from E1) occurs in the range A1:C10.

    =COUNTIF($A$1:$C$10;$E$1) -- that's no problem.


    But I'd like to use a scroll bar and based on the value of the scroll bar, I'd like to have that count limit the number of rows that are taken in the range for calculation. I'm attaching the spreadsheet for easier understanding.


    Thanks!

    You can use:


    =COUNTIF(A1:INDEX($C$1:$C$10,$H1),E1)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Love 1

    Thank you!

    Now I realized where my mistake was in my own attempts. The INDEX formula's range A1:C10 is defined with just the last column! Thanks!

    You could also have used:


    =COUNTIF(A1:INDEX($A$1:$C$10,$H1,3),E1)


    if you wanted.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Love 1

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login