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

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

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

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

"nir020" wrote in message
...
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

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

Suppose you have the sheet name in A1

=COUNTIF(INDIRECT(A1&"!A3:A7"),$A$2)
--
If this post helps click Yes
---------------
Jacob Skaria


"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

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

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