I have a reference to an Excel table in another sheet, like this:

='Day-Out-17-HYS.xlsx'!TblSmmry[[#Totals],[Date]]



and would like to replace the HYS above with a cell reference such as B17 so that copying this formula to other rows would access different external sheets.

I've tried various combinations of "&B17&" with/without double quotes around the sheet name, but haven't yet found the right way to do this

i.e. ="'Day-Out-17-"&B17&".xlsx'"!TblSmmry[[#Totals],[Date]] and other variations get There is a problem with this formula error.

Hi
have a look at the INDIRECT function

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks, that looks as though it could work but I see that INDIRECT needs all the source workbooks to be open.

I currently have over 20 such workbooks, each with 6 worksheets. So I think that's impractical.

Thanks anyway, I'll keep it in mind.

It is not INDIRECT that needs the books to be opened, but the function you use. SUMPRODUCT e.g. will work on closed books even when INDIRECT is used within the function.
As for your formula, perhaps VBA will do the trick, but that is above my pay grade

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I feel I am progressing here, a little.

I can use INDIRECT within SUBTOTAL(105,xxx) to get the results I'm looking for but all the workbooks still need to be open.

Haven't yet figured out how to use SUMPRODUCT to do the same.

Found a solution:

It took me a few hours to amend all 20+ workbooks to put required data in one particular sheet in each workbook.

Now Power Query can provide the summary report as wanted.

Nice to see you found a solution.
Please mark thread as solved. Thx

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post