Forum Discussion
How do I create a summary page that pulls data from a chosen date on another tab please?
Hi,
I need to create a spreadsheet for monitoring supply levels. Each day stock levels will be added (AM and PM). I call this my 'Daily Checks' sheet. This bit is easy, but I need a 'Live Balance' sheet where I can enter any date, and it will pull over that dates 2 rows of data from the Stock sheet.
For example, in my attached file, if I enter 02/01/2022 in the 'Live Balance', cell B3, I want Rows E & F to populate with the figures from the 02/01/2022 rows (both AM and PM) from the 'Daily Checks' sheet. The idea is I can type any date in and see the stock data from that date.
Once I have that data populated in 'Live Balance' Rows E & F I will be able to add some formulas to analyse change and values in Rows G, H, I etc.
The part I am struggling with is how to get the Live Balance Rows E-F to populate when entering the date in A3.
Erm, help please!?! I'm using Excel 2016 64-bit.
This could be
=OFFSET('Daily Checks'!$C$4,ROW()-5,MATCH('Live Balance'!$B$3,'Daily Checks'!$C$4:$ABD$4,0)-1,1,1)
and
=OFFSET('Daily Checks'!$C$4,ROW()-5,MATCH('Live Balance'!$B$3,'Daily Checks'!$C$4:$ABD$4,0),1,1)
4 Replies
As variant
=IF( $D8 = "", "", INDEX( 'Daily Checks'!$C$6:$ABD$299, MATCH($D8, 'Daily Checks'!$B$6:$B$299, 0), MATCH( $B$3 & E$6, 'Daily Checks'!$C$4:$ABD$4 & 'Daily Checks'!$C$5:$ABD$5, 0) ) )not to sync names positions in both sheets.
- Hi Sergei, Thank you too for your suggestion. Really helpful!
This could be
=OFFSET('Daily Checks'!$C$4,ROW()-5,MATCH('Live Balance'!$B$3,'Daily Checks'!$C$4:$ABD$4,0)-1,1,1)
and
=OFFSET('Daily Checks'!$C$4,ROW()-5,MATCH('Live Balance'!$B$3,'Daily Checks'!$C$4:$ABD$4,0),1,1)
OliverScheurich Thank you! This was just what I needed.
