SOLVED

How do I create a summary page that pulls data from a chosen date on another tab please?

Copper Contributor

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.

 

4 Replies
best response confirmed by SteveB1980 (Copper Contributor)
Solution

@SteveB1980 

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)

@SteveB1980 

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!
1 best response

Accepted Solutions
best response confirmed by SteveB1980 (Copper Contributor)
Solution

@SteveB1980 

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)

View solution in original post