Posts by GradB

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!

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!

I have 10 rows, say A1 to A10, all have dates in them.

I'd like to identify in which row the oldest date is.

I know I need to link =ROW and =MIN(A1:A10) into one single formula but not sure how.

Also, would the formula work if there are any blank cells between A1 and A10?

Thanks.

rory

Does it really matter whether one uses a scroll bar or a spin button?

Thank you, Rory, I'll try that, too!

Appreciate it!

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!

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.