SOLVED

New Contributor

# 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.

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

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

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)

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

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.

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

@Quadruple_Pawn Thank you! This was just what I needed.