Use different sheet names as part of a formula (indirect function?)

[neuneph]

Hi,
I have a workbook with several sheets, which all have data in the same format but with different numbers.

I am trying to create a summary sheet, which pulls the data from each individual workbook and lists it.
As VBAs are disabled and I have never created a macro before, I am trying to solve it with a formula.

The idea is to create an overview like this where I input the different Sheet Names (Column A) and all the columns next to it get updated automatically with the sheet name.

This is the functioning formula in Cell B3: ='Dreamies 60g KW 29'!A$5
In Cell B2 I tried to use an indirect function but it returns a REF error: =INDIRECT(A2&"!A$5";TRUE)

Is there a way to fix the indirect formula so that it works just like the one in Cell B3?

Thanks a lot for your help!

 

[awoohaw]

If the sheet name has a space in it, it needs to be wrapped in single quotes. precede the cell reference with a Exclamation Point.
so cell B4 in Sheet5 is Sheet5!B4,
cell B4 is Sheet Five is 'Sheet Five'!B4
Use the $ sign as appropriate for your absolute references.

So in your question try this;
=INDIRECT("'" & A2 &"'!A$5")

But a better way to do your task may be to use Power Query. Here is a video on it:

 

[neuneph]

Thanks a lot that worked!

 

[awoohaw]

Thanks a lot that worked!

You're welcome.