Nov 13 2021 09:41 AM
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.
Nov 13 2021 10:11 AM
SolutionThis 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)
Nov 13 2021 11:42 AM
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.
Nov 15 2021 07:37 AM
@OliverScheurich Thank you! This was just what I needed.
Nov 15 2021 07:38 AM
Nov 13 2021 10:11 AM
SolutionThis 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)