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

[jwillits]

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.

 

[Eric W]

Something like this:

Book1
	A	B	C	D	E
1	Sheet2	Sheet4			Sheet1
2			5		Sheet2
3	9				Sheet3
4					Sheet4
5					Sheet5
Sheet5

Cell Formulas
Range	Formula
A3	A3	=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
Cell	Allow	Criteria
A1:B1	List	=$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.