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' s...

Restaurant.xlsx27 KB

BI & Data Analysis

Formulas and Functions

OliverScheurich
Nov 13, 2021
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)
Restaurant.xlsx28 KB

SergeiBaklan

Diamond Contributor

Nov 13, 2021

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.

Restaurant.xlsx31 KB

SteveB1980

Copper Contributor

Nov 15, 2021

Hi Sergei, Thank you too for your suggestion. Really helpful!