Dynamic sheet name in formula

[sharpnova]

I have a workbook that is running a SUMPRODUCT on another workbook.

Here is the formula:

=SUMPRODUCT(--('U:\My Documents\[workbook1.xls]sheet1'!$C$3:$C$12=$A2),--('U:\My Documents\[workbook1.xls]sheet1'!H$3:H$12="no"))

This formula works great.

But I would like the sheet name to, instead of being hardcoded as "sheet1" draw from cell A1 to get the name of the sheet to be used.

I have tried this and it gives an immediate error:


=SUMPRODUCT(--('U:\My Documents\[workbook1.xls]&A1'!$C$3:$C$12=$A2),--('U:\My Documents\[workbook1.xls]&A1'!H$3:H$12="no"))

[NBVC]

You will need INDIRECT function for that... and that function does not work with closed workbooks. Is that ok? and if not, there is a free addin that you can use, is that ok?

[sharpnova]

You will need INDIRECT function for that... and that function does not work with closed workbooks. Is that ok? and if not, there is a free addin that you can use, is that ok?

Unfortunately, neither of those are ok. Workbooks need to be closed and an addon won't be possible either.

Are you sure there's no way to modify my formula?

The formula already correctly grabs data from a closed workbook. All it needs to do is get the "Sept 7" part of its string from a cell.

[NBVC]

Unfortunately, you need to refer to that cell in an indirect manner..

here are your options: INDIRECT alternatives for Outside Workbooks