Copy a certain set of data from one sheet to another depending on the date typed in

[Emily1996]

hi guys, hoping someone can help me figure this one out.
i have a work book with 2 sheets in it, one is a "Master" sheet titled Calcs(2) and another is a general sheet titled Stopes 2024-2025.
We have a range of people that will be inputting data into the "Stopes" sheet, this will happen every week - they manually input the start date in cell B2, B26, B50 etc (they fill in a new group of information every week) and then fill in other relevant information.
As you can see in the screen shot, there are multiple copies of the same sheet for people to fill out as this is done every single week. (the data runs for 4 weeks but is freshly input every week, as after the first week its all just a rough guess)
i then need to be able to type a specific date in cell B2 in my master sheet Calcs(2), and have it pull the information from Stopes 2024-2025 that correspond with the date typed in. the main table runs from cells A1-BH23, but then within the stopes sheet i have got 52 copies of it so those cell ranges change with each date range.

its very important that when i import it to the master Calcs sheet that all the information is in the right cells as all the informaion below in the calcs sheet has formulas that rely on them being in the correct spot.

i hope what i have written makes sense, and hopefully someone out there can help me! thank you.

Calcs(02) Sheet

Stopes 2024-2025 Sheet

 

[myall_blues]

Ahh backfill - that takes me back!

Try this formula in cell E7 of your Calcs(02) sheet. You will need to change the $B$100 to wherever the bottom of your data are on the stopes sheet.

=OFFSET(INDIRECT(INDEX(TEXTSPLIT(CELL("address",XLOOKUP($B2,'Stopes 2024-2025'!$B$2:$B$100,'Stopes 2024-2025'!$B$2:$B$100)),"]"),2)),5,3,16,56)

 

[myall_blues]

Sorry - made a minor error. Try this instead:

=LET(sname,INDEX(TEXTSPLIT(CELL("address",XLOOKUP($B2,'Stopes 2024-2025'!$B$2:$B$100,'Stopes 2024-2025'!$B$2:$B$100)),"]"),2),OFFSET(INDIRECT("'" & INDEX(TEXTSPLIT(sname,"!"),1) & "!" & INDEX(TEXTSPLIT(sname,"!"),2)),5,3,16,56))

 

[Emily1996]

Sorry - made a minor error. Try this instead:

=LET(sname,INDEX(TEXTSPLIT(CELL("address",XLOOKUP($B2,'Stopes 2024-2025'!$B$2:$B$100,'Stopes 2024-2025'!$B$2:$B$100)),"]"),2),OFFSET(INDIRECT("'" & INDEX(TEXTSPLIT(sname,"!"),1) & "!" & INDEX(TEXTSPLIT(sname,"!"),2)),5,3,16,56))

Thank you so much! Sorry for my delayed response I ended up being off work with pneumonia and wasn't able to test the formula.

The formula has worked great, the only problem I have now is that it didn't pull the stope names. It pulled all the other data needed, just not the stope names. I'm trying to understand the formula on where I need to make the change for it to pull those details, but hoping you might reply with an answer quicker than it will take me to figure it out

 

[myall_blues]

That’s nasty. I hope you’re feeling better.

Where do you want the stope name - in the cells in column C?

 

[Emily1996]

Yes, in column C - where the Orange box I highlighted

 

[myall_blues]

That one's a bit more tricky but I think this is it.
Put this formula in C6 on the CALCS sheet then copy it to the other orange ones.

=OFFSET(INDIRECT("'" & "Stopes 2024-2025" & "'!" & CELL("address",$E7)),-1,-2)

 

[Emily1996]

Tha

That one's a bit more tricky but I think this is it.
Put this formula in C6 on the CALCS sheet then copy it to the other orange ones.

=OFFSET(INDIRECT("'" & "Stopes 2024-2025" & "'!" & CELL("address",$E7)),-1,-2)

thank you! I tried putting this in, and while it does pull from the first "table", it doesn't pull from any other set of data with a different date (so if you refer back to that original image I sent of the stopes sheet, if I have data in the second "table" with a different date, it won't pull the "stope names" from that set of data, it only pulls from the original "first table"

 

[myall_blues]

I see. How about this then? As before, in C6 on the CALCS sheet then copied to the other orange 'Stope name' cells. Also change $B$100 to suit your bottom row.

=XLOOKUP($B$2,'Stopes 2024-2025'!$B$2:$B$100,OFFSET('Stopes 2024-2025'!$B$2:$B$100,4,1,MAX(ROW('Stopes 2024-2025'!$B$2:$B$100))-MIN(ROW('Stopes 2024-2025'!$B$2:$B$100))+1,1))

 

[Emily1996]

Thank you so much! You have been such an amazing help and i really really appreciate it.

I only had to change the range number when I put the formula into the other "stope name" boxes (example 4 changed to 8 and then 12 for the second and 3rd boxes - just thought I'd add that in there incase anyone finds this thread in future and needs a similar formula)

I see. How about this then? As before, in C6 on the CALCS sheet then copied to the other orange 'Stope name' cells. Also change $B$100 to suit your bottom row.

=XLOOKUP($B$2,'Stopes 2024-2025'!$B$2:$B$100,OFFSET('Stopes 2024-2025'!$B$2:$B$100,4,1,MAX(ROW('Stopes 2024-2025'!$B$2:$B$100))-MIN(ROW('Stopes 2024-2025'!$B$2:$B$100))+1,1))