Formula to automate sheet name

[diamondjoechubbs]

It has been a while since I've used excel in a job setting, so forgive me as I am trying to dust off the cobwebs.
Every morning I import data from a report into a new sheet that I name simply with that date. I then use the following formula to pull the information I need from that sheet into my data tracking sheet =XLOOKUP($D2,'10.23.2020'!$A:$A,'10.23.2020'!$D:$D,"D.N.W.")
Is there a way, since the sheet name is only the date, which is also the first row on that spreadsheet, to automate the sheet name so that it pulls it every day so that I don't have to find and replace?

 

[Joe4]

Welcome to the Board!

You can use the INDIRECT function to dynamically build your range reference (see: Excel INDIRECT Function Examples and Videos).

So if the name of your sheet was in cell A2 (i.e. "10.23.2020" is in cell A2), then to dynamically build this range:
'10.23.2020'!$A:$A
you would use:

INDIRECT("'" & A2 & "'!A:A")

 

[Fluff]

Hi & welcome to MrExcel.
There is, but that involves using the Indirect function which is volatile & very slow.

INDIRECT – Excel’s Most Evil Function

My choice for Excel’s most evil worksheet function is INDIRECT, for reasons outlined below. If you have a different choice you can cast your vote for your most evil function (and your favouri…

fastexcel.wordpress.com

 

[diamondjoechubbs]

So Just to make sure I am understanding correctly, I would replace '10.23.2020' in the xlookup with INDIRECT("'" & A1 & "'!A:A") if the value of the cell is in A1?

 

[Fluff]

That's right.
And then the same again for the return range.

 

[Joe4]

So Just to make sure I am understanding correctly, I would replace '10.23.2020' in the xlookup with INDIRECT("'" & A1 & "'!A:A") if the value of the cell is in A1?

Just like I showed in my post, not just the '10.23.2020' part, but all of: '10.23.2020'!$A:$A

To make it easier to understand, you can approach it this way.
Create a dynamic formula to return a string that looks exactly like the range you want.
Then, once you have that formula built, simply surround it in INDIRECT(...)

 

[diamondjoechubbs]

I am sorry, I know I am doing something wrong, but I am at a loss at pulling the cobweb outs

=XLOOKUP($D8,INDIRECT( '& I1 &',"!I:I")!$A:$A,INDIRECT( '& I1 &', "!I:I")!$D:$D,"D.N.W.")

 

[Fluff]

It needs to be like

=XLOOKUP($D8,INDIRECT("'"& I1 &"'!A:A"),INDIRECT("'"& I1 &"'!D:D"),"D.N.W.")

 

[diamondjoechubbs]

It needs to be like

=XLOOKUP($D8,INDIRECT("'"& I1 &"'!A:A"),INDIRECT("'"& I1 &"'!D:D"),"D.N.W.")

The function works, but it is not pulling the correct data... so basically my master sheet has their ID in cell D8, then it needs to go to sheet 10.23.2020 and search for that ID in column A and pull the data from column D.
When I type in 10.23.2020 it works and pulls the correct data, but when I try the indirect function it does not

 

[Fluff]

In what way is it not pulling the correct data?