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


On 6 Maj, 13:41, nir020 wrote:
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