Using a cell containing a sheet name in a formula to pull from that worksheet

[VN5968]

Hello. I have created a Macro that creates a new worksheet based on a range of cells. So in row 5 I have all the weeks e.g. 01.04.21 and this has automatically set up a new worksheet called 01.04.21.
Is there a way I can use the cells in row 5 in a formula so each column pulls information from the worksheet named in row 5 of that column.

each of these dates above have created a worksheet named the same. Is there a formula or VBA code that I can put in that says look at A5 (the date), find the worksheet with the same name and tell me what is in K20 on that worksheet?

Thanks in advance

 

[Joe4]

Welcome to the Board!

Yes, you can do this using the INDIRECT function.
See this link for an example of precisely what you want to do: Excel formula: Indirect named range different sheet | Exceljet

 

[VN5968]

Thanks Joe. I am a little lost as to what the "range Data" or "name" is referring to on sheet 1? Any ideas?

 

[Joe4]

Thanks Joe. I am a little lost as to what the "range Data" or "name" is referring to on sheet 1? Any ideas?

You said that you wanted to pull cell "K20" from that sheet, right?
Then that value would just be "K20".

INDIRECT may make more sense if you approach it this way.
You can manually create a reference to the cell you want (just type in an equal sign, and go to the sheet and cell you want and hit ENTER).
You can view this reference to see what it looks like.
Now, create a formula that "builds" this reference so it matches exactly (pulling from the cell with the date/sheet name in it, and hard-coding the range).
Once you have it looking exactly the way you need it to, surround that formula in the INDIRECT function to convert it from a string to a valid reference.

 

[VN5968]

Here is what I have typed but it is coming up as #REF! C5 being the name of the sheet (01.04.21) and K20 being the cell from 01.04.21 that I want to sum. Have I missed something?

 

[Joe4]

K20 need to be in-between double-quotes!
Otherwise you are pulling the value from K20 on that sheet in building your reference.

As I recommended, try building it without the INDIRECT first to make sure that you have it working correctly, i.e.
compare what this returns (what you had):

="'"&C5&"'!" & K20

with this:

="'"&C5&"'!K20"

The second one builds the reference exactly the way you need it to look.
So then just enclose it in the INDIRECT function.

Note, I am not sure why you are adding the SUM function to the equation. That should not be necessary.

 

[VN5968]

K20 need to be in-between double-quotes!
Otherwise you are pulling the value from K20 on that sheet in building your reference.

As I recommended, try building it without the INDIRECT first to make sure that you have it working correctly, i.e.
compare what this returns (what you had):

="'"&C5&"'!" & K20

with this:

="'"&C5&"'!K20"

The second one builds the reference exactly the way you need it to look.
So then just enclose it in the INDIRECT function.

Note, I am not sure why you are adding the SUM function to the equation. That should not be necessary.

Perfect! thanks for all your help Joe. I have taken the SUM function out as well

 

[Joe4]

You are welcome. Glad I was able to help.

Yes, since you are only pulling back one number, there is no need for the SUM function. That is used to sum up multiple values/ranges.