# Is it possible to input a cells value into a formula automatically?

#### jwillits

##### New Member
For instance, say I wanted to sum the data from a range of sheets...

=sum(sheet1:sheet5!C2)

...summing C2 across those sheets, however I would like to change the range of those sheets often (sheet1 to sheet3, sheet 4 to sheet5, etc).

Is it possible to make a data validation drop down to automatically change the initial formula to what I want?

For instance, my drop down would be..

Sheet1
Sheet2
Sheet3
Sheet4
Sheet5

And when I select one of these, it changes the bold part of this formula to the sheet picked from the list.

=sum(sheet1:sheet5!C2)

Obviously I would have 2 dropdown validations to change the value on each side of the formula.

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Eric W

##### MrExcel MVP
Something like this:

Book1
ABCDE
1Sheet2Sheet4Sheet1
25Sheet2
39Sheet3
4Sheet4
5Sheet5
Sheet5
Cell Formulas
RangeFormula
A3A3=SUMPRODUCT(SUBTOTAL(9,(INDIRECT("'"&INDEX(E1:E5,MATCH(A1,E1:E5,0)):INDEX(E1:E5,MATCH(B1,E1:E5,0))&"'!C2"))))
Cells with Data Validation
CellAllowCriteria
A1:B1List=\$E\$1:\$E\$5

Set up a list of your sheets (E1:E5). Use that to build your Data Validation drop-downs (A1:B1). Then the A3 formula should work for you.

Replies
9
Views
160
Replies
9
Views
312
Replies
0
Views
120
Replies
9
Views
826
Replies
29
Views
524

1,147,517
Messages
5,741,631
Members
423,674
Latest member
Charles2dodo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back