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

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
 
J

Jacob Skaria

Suppose you have the sheet name in A1

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top