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

  • Thread starter Thread starter nir020
  • Start date Start date
N

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
 
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
 
Back
Top