SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2961894%22%20slang%3D%22en-US%22%3EHow%20do%20I%20create%20a%20summary%20page%20that%20pulls%20data%20from%20a%20chosen%20date%20on%20another%20tab%20please%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2961894%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20need%20to%20create%20a%20spreadsheet%20for%20monitoring%20supply%20levels.%20Each%20day%20stock%20levels%20will%20be%20added%20(AM%20and%20PM).%20I%20call%20this%20my%20'Daily%20Checks'%20sheet.%20This%20bit%20is%20easy%2C%20but%20I%20need%20a%20'Live%20Balance'%20sheet%20where%20I%20can%20enter%20any%20date%2C%20and%20it%20will%20pull%20over%20that%20dates%202%20rows%20of%20data%20from%20the%20Stock%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20in%20my%20attached%20file%2C%20if%20I%20enter%2002%2F01%2F2022%20in%20the%20'Live%20Balance'%2C%20cell%20B3%2C%20I%20want%20Rows%20E%20%26amp%3B%20F%20to%20populate%20with%20the%20figures%20from%20the%2002%2F01%2F2022%20rows%20(both%20AM%20and%20PM)%20from%20the%20'Daily%20Checks'%20sheet.%20The%20idea%20is%20I%20can%20type%20any%20date%20in%20and%20see%20the%20stock%20data%20from%20that%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20I%20have%20that%20data%20populated%20in%20'Live%20Balance'%20Rows%20E%20%26amp%3B%20F%20I%20will%20be%20able%20to%20add%20some%20formulas%20to%20analyse%20change%20and%20values%20in%20Rows%20G%2C%20H%2C%20I%20etc.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20part%20I%20am%20struggling%20with%20is%20how%20to%20get%20the%20Live%20Balance%20Rows%20E-F%20to%20populate%20when%20entering%20the%20date%20in%20A3.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EErm%2C%20help%20please!%3F!%20I'm%20using%20Excel%202016%2064-bit.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2961894%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2961929%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20summary%20page%20that%20pulls%20data%20from%20a%20chosen%20date%20on%20another%20tab%20please%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2961929%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1217531%22%20target%3D%22_blank%22%3E%40SteveB1980%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20could%20be%3C%2FP%3E%3CP%3E%3DOFFSET('Daily%20Checks'!%24C%244%2CROW()-5%2CMATCH('Live%20Balance'!%24B%243%2C'Daily%20Checks'!%24C%244%3A%24ABD%244%2C0)-1%2C1%2C1)%3C%2FP%3E%3CP%3Eand%3C%2FP%3E%3CP%3E%3DOFFSET('Daily%20Checks'!%24C%244%2CROW()-5%2CMATCH('Live%20Balance'!%24B%243%2C'Daily%20Checks'!%24C%244%3A%24ABD%244%2C0)%2C1%2C1)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2962072%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20summary%20page%20that%20pulls%20data%20from%20a%20chosen%20date%20on%20another%20tab%20please%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2962072%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1217531%22%20target%3D%22_blank%22%3E%40SteveB1980%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(%20%24D8%20%3D%20%22%22%2C%20%22%22%2C%0A%20%20%20%20%20%20INDEX(%20'Daily%20Checks'!%24C%246%3A%24ABD%24299%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20MATCH(%24D8%2C%20'Daily%20Checks'!%24B%246%3A%24B%24299%2C%200)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20MATCH(%20%24B%243%20%26amp%3B%20E%246%2C%20'Daily%20Checks'!%24C%244%3A%24ABD%244%20%26amp%3B%20'Daily%20Checks'!%24C%245%3A%24ABD%245%2C%200)%20)%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Enot%20to%20sync%20names%20positions%20in%20both%20sheets.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New 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!