Looking up a worksheet and value

[jtfalk]

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.

 

[Bernie Deitrick]

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

 

[Sean Timmons]

Try the below

=COUNTIF(INDIRECT(A1&"!D41817"),D2)