Indirect reference to a sheet in this formula

[Macdave_19]

i guys,

I need some help please.

i have a drop down list of all the worksheets in the workbook in cell A1.

I want to reference this in my formula below:

=SUM((#REF!$H$1:$H$10000=""&$B$4)*(#REF!$L$1:$L$10000=""&G4))

the formula is a countif with 2 criteria really.

I need to do this so that i can slect wich data set to count from.

please help?

i thought it would be like this or using the =INDIRECT() function but i cant figure it out!!

=SUM((A1&"!"&$H$1:$H$10000=""&$B$4)*(A1$"!"&$L$1:$L$10000=""&G4))

thanks for reading this tread everyone.

[NBVC]

Try:

=SUMPRODUCT((INDIRECT("'"&A1&"'!$H$1:$H$10000")=$B$4)*((INDIRECT("'"&A1&"'!$L$1:$L$10000")=G4)))

[Macdave_19]

Not working Buddy i get a Ref# error?

any other ideas?

[NBVC]

The sheetname in cell A1 must be exactly the same as the actual sheetname in your workbook (ie. spaces too!).

Did you double-check that?

[Macdave_19]

They are buddy can i send you the file? whats ur e-mail addy?

[NBVC]

Can you upload it here as a zipped XL2003 file?