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

jwillits

New Member
Joined
Jul 10, 2018
Messages
34
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
11,655
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top