Looking up a worksheet and value
 

I am trying to have a graph set up to look at 1 of 18 worksheets to get the
numbers and graph it. Currently I have this for populatiing the numbers

D2 = week 1
=COUNTIF('Dept. 1'!$D$4:$D$1817,D2)

I have made a drop down that has Dept. 1 trough 18 in cell A1. I would like
to have the statement be that which would look at A1 and pull the column D
values from the cooresponding worksheet to A1. Any help would be appreciated.

=COUNTIF('A1'!$D$4:$D$1817,D2) - does not work because there is no A1
woorksheet.

Looking up a worksheet and value
 

=COUNTIF(INDIRECT("'" & A1 & "'!$D$4:$D$1817"),D2)
HTH,
Bernie
MS Excel MVP


"jtfalk" wrote in message
...
I am trying to have a graph set up to look at 1 of 18 worksheets to get the
numbers and graph it. Currently I have this for populatiing the numbers

D2 = week 1
=COUNTIF('Dept. 1'!$D$4:$D$1817,D2)

I have made a drop down that has Dept. 1 trough 18 in cell A1. I would like
to have the statement be that which would look at A1 and pull the column D
values from the cooresponding worksheet to A1. Any help would be appreciated.

=COUNTIF('A1'!$D$4:$D$1817,D2) - does not work because there is no A1
woorksheet.

Looking up a worksheet and value
 

Try the below

=COUNTIF(INDIRECT(A1&"!D4:D1817"),D2)

"jtfalk" wrote:

I am trying to have a graph set up to look at 1 of 18 worksheets to get the
numbers and graph it. Currently I have this for populatiing the numbers

D2 = week 1
=COUNTIF('Dept. 1'!$D$4:$D$1817,D2)

I have made a drop down that has Dept. 1 trough 18 in cell A1. I would like
to have the statement be that which would look at A1 and pull the column D
values from the cooresponding worksheet to A1. Any help would be appreciated.

=COUNTIF('A1'!$D$4:$D$1817,D2) - does not work because there is no A1
woorksheet.