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

Emily1996

New Member
Joined
Aug 9, 2024
Messages
5
Office Version
  1. 2021
Platform
  1. Windows
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
calcs tab.PNG




Stopes 2024-2025 Sheet
stopes tab.PNG
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.

Excel Formula:
=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)
 
Last edited:
Upvote 0
Sorry - made a minor error. Try this instead:
Excel Formula:
=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))
 
Upvote 1
Solution
Sorry - made a minor error. Try this instead:
Excel Formula:
=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 😂
 
Upvote 0
That’s nasty. I hope you’re feeling better.

Where do you want the stope name - in the cells in column C?
 
Upvote 0
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.
Excel Formula:
=OFFSET(INDIRECT("'" & "Stopes 2024-2025" & "'!" & CELL("address",$E7)),-1,-2)
 
Upvote 0
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.
Excel Formula:
=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"
 
Upvote 0
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.

Excel Formula:
=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))
 
Upvote 0
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.

Excel Formula:
=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))
 
Upvote 0

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top