How can I get a drop down to update the range of a formula

[nir020]

I have created the following formula:-

=COUNTIF(Sheet1!A3:A7,$A$2)

What I would like to do is create a countif formula looks in different
sheets (but at the same range) based on what the users has selected in a drop
down.

So if the users has selected from the drop down Sheet1 the formula looks in
sheet1, if the users selects Sheet2 the formula looks in Sheet2

Can you help

Thanks

 

[Ashish Mathur]

Hi,

you can try this

=countif(indirect(A1&"!A3:A7"),$A$2)

where A1 holds Sheet1

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Jacob Skaria]

Suppose you have the sheet name in A1

=COUNTIF(INDIRECT(A1&"!A3:A7"),$A$2)

 

[Jarek Kujawa]

Excel 2003
select B1 then
Data->Validation->(Allow window) select List->show yr range containing
list of worksheets available->OK
then use the formula:
=COUNTIF(INDIRECT(B1&"!A3:A7");$A$2)

adjust B1 to suit