Home

Pull Data from Specific Cells based on Date

%3CLINGO-SUB%20id%3D%22lingo-sub-729455%22%20slang%3D%22en-US%22%3EPull%20Data%20from%20Specific%20Cells%20based%20on%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-729455%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20working%20on%20creating%20a%20reusable%20performance%20tracker%20for%20my%20company%20that%20tracks%20sales%20of%20different%20criteria%20New%20Activation%20(NAC)%20vs.%20Upgrade%20(HUP)%20based%20on%20company.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20several%20sheets%20that%20are%20dedicated%20for%20each%20Employee's%20sales%20(and%20are%20identical%20minus%20values%20inputted%20in%20their%20own%20trackers)%2C%20and%20then%20one%20sheet%20that%20I%20current%20have%20called%20%22Back%20End%22%20where%20I%20can%20then%20collect%20information%20to%20then%20generate%20graphs%20from.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20situation%2C%20is%20that%20on%20the%20Back%20End%20sheet%2C%20I%20have%20a%20%22Today's%20Sales%22%20graph%20(shown%20in%20Excel1.PNG)%20that%20I%20want%20to%20summarize%20the%20total%20number%20of%20NAC%20and%20HUP%20sales%20per%20company%20across%20all%20employees%2C%20that%20will%20dynamically%20switch%20to%20pull%20from%20the%20proper%20group%20of%20cells%20when%20the%20date%20changes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel2.PNG%20depicts%20an%20example%20of%20an%20employee's%20specific%20worksheet.%20There%20are%205%20groups%20of%207%20daily%20tables.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20essentially%20what%20I%20want%2C%20is%20for%20the%20table%20on%20Excel1.PNG%20to%20reflect%20the%20sum%20of%20the%20data%20of%20the%20tables%20for%20EX.%20%5B26-May-19%5D%20on%20each%20employee's%20sheet%20if%20the%20current%20date%20is%20%5B26-May-19%5D%2C%20and%20to%20dynamically%20change%20when%20the%20date%20changes%20to%20refer%20to%20each%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20could%20help%20me%20I%20would%20greatly%20appreciate%20it.%20I've%20been%20trying%20to%20research%20VLOOKUP%2C%20INDEX%2C%20MATCH%2C%20etc%20to%20see%20if%20that's%20what%20I%20need%20to%20do%20but%20honestly%20I'm%20getting%20fairly%20confused.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-729455%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-730134%22%20slang%3D%22en-US%22%3ERe%3A%20Pull%20Data%20from%20Specific%20Cells%20based%20on%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-730134%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368641%22%20target%3D%22_blank%22%3E%40JonComeau%3C%2FA%3E%26nbsp%3BIf%20you%20are%20open%20to%20change%20the%20way%20you%20present%20information%2C%20you%20can%20use%20pivot%20tables.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368641%22%20target%3D%22_blank%22%3E%40JonComeau%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20working%20on%20creating%20a%20reusable%20performance%20tracker%20for%20my%20company%20that%20tracks%20sales%20of%20different%20criteria%20New%20Activation%20(NAC)%20vs.%20Upgrade%20(HUP)%20based%20on%20company.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20several%20sheets%20that%20are%20dedicated%20for%20each%20Employee's%20sales%20(and%20are%20identical%20minus%20values%20inputted%20in%20their%20own%20trackers)%2C%20and%20then%20one%20sheet%20that%20I%20current%20have%20called%20%22Back%20End%22%20where%20I%20can%20then%20collect%20information%20to%20then%20generate%20graphs%20from.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20situation%2C%20is%20that%20on%20the%20Back%20End%20sheet%2C%20I%20have%20a%20%22Today's%20Sales%22%20graph%20(shown%20in%20Excel1.PNG)%20that%20I%20want%20to%20summarize%20the%20total%20number%20of%20NAC%20and%20HUP%20sales%20per%20company%20across%20all%20employees%2C%20that%20will%20dynamically%20switch%20to%20pull%20from%20the%20proper%20group%20of%20cells%20when%20the%20date%20changes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel2.PNG%20depicts%20an%20example%20of%20an%20employee's%20specific%20worksheet.%20There%20are%205%20groups%20of%207%20daily%20tables.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20essentially%20what%20I%20want%2C%20is%20for%20the%20table%20on%20Excel1.PNG%20to%20reflect%20the%20sum%20of%20the%20data%20of%20the%20tables%20for%20EX.%20%5B26-May-19%5D%20on%20each%20employee's%20sheet%20if%20the%20current%20date%20is%20%5B26-May-19%5D%2C%20and%20to%20dynamically%20change%20when%20the%20date%20changes%20to%20refer%20to%20each%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20could%20help%20me%20I%20would%20greatly%20appreciate%20it.%20I've%20been%20trying%20to%20research%20VLOOKUP%2C%20INDEX%2C%20MATCH%2C%20etc%20to%20see%20if%20that's%20what%20I%20need%20to%20do%20but%20honestly%20I'm%20getting%20fairly%20confused.%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-730464%22%20slang%3D%22en-US%22%3ERe%3A%20Pull%20Data%20from%20Specific%20Cells%20based%20on%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-730464%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368641%22%20target%3D%22_blank%22%3E%40JonComeau%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EThe%20situation%20is%20not%20clear%20enough%20to%20me%20since%20you%20say%20that%20you%20have%20a%20separate%20sheet%20for%20each%20employee%20but%20in%20the%20screenshot%20I%20see%20all%20of%20them%20in%20one%20single%20sheet.%20sending%20a%20real%20excel%20sheet%20with%20just%20some%20irrelevant%20test%20numbers%20makes%20easier%20for%20anyone%20to%20help%20you.%3C%2FP%3E%3CP%3Eif%20you%20are%20talking%20about%20consolidating%20multiple%20worksheets%20(or%20hundreds%20of%20Sheets)%20together%20here%20is%20a%20great%20solution%20that%20I%20just%20published%20on%20my%20YouTube%20channel%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DkVA8K4QZvYw%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DkVA8K4QZvYw%3C%2FA%3E%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E
JonComeau
Occasional Visitor

Hello,

 

I'm working on creating a reusable performance tracker for my company that tracks sales of different criteria New Activation (NAC) vs. Upgrade (HUP) based on company.

 

I have several sheets that are dedicated for each Employee's sales (and are identical minus values inputted in their own trackers), and then one sheet that I current have called "Back End" where I can then collect information to then generate graphs from.

 

My situation, is that on the Back End sheet, I have a "Today's Sales" graph (shown in Excel1.PNG) that I want to summarize the total number of NAC and HUP sales per company across all employees, that will dynamically switch to pull from the proper group of cells when the date changes.

 

Excel2.PNG depicts an example of an employee's specific worksheet. There are 5 groups of 7 daily tables.

 

So essentially what I want, is for the table on Excel1.PNG to reflect the sum of the data of the tables for EX. [26-May-19] on each employee's sheet if the current date is [26-May-19], and to dynamically change when the date changes to refer to each date.

 

If anyone could help me I would greatly appreciate it. I've been trying to research VLOOKUP, INDEX, MATCH, etc to see if that's what I need to do but honestly I'm getting fairly confused.

2 Replies

@JonComeau If you are open to change the way you present information, you can use pivot tables.  


@JonComeau wrote:

Hello,

 

I'm working on creating a reusable performance tracker for my company that tracks sales of different criteria New Activation (NAC) vs. Upgrade (HUP) based on company.

 

I have several sheets that are dedicated for each Employee's sales (and are identical minus values inputted in their own trackers), and then one sheet that I current have called "Back End" where I can then collect information to then generate graphs from.

 

My situation, is that on the Back End sheet, I have a "Today's Sales" graph (shown in Excel1.PNG) that I want to summarize the total number of NAC and HUP sales per company across all employees, that will dynamically switch to pull from the proper group of cells when the date changes.

 

Excel2.PNG depicts an example of an employee's specific worksheet. There are 5 groups of 7 daily tables.

 

So essentially what I want, is for the table on Excel1.PNG to reflect the sum of the data of the tables for EX. [26-May-19] on each employee's sheet if the current date is [26-May-19], and to dynamically change when the date changes to refer to each date.

 

If anyone could help me I would greatly appreciate it. I've been trying to research VLOOKUP, INDEX, MATCH, etc to see if that's what I need to do but honestly I'm getting fairly confused.


 

Highlighted

@JonComeau 

Hi

The situation is not clear enough to me since you say that you have a separate sheet for each employee but in the screenshot I see all of them in one single sheet. sending a real excel sheet with just some irrelevant test numbers makes easier for anyone to help you.

if you are talking about consolidating multiple worksheets (or hundreds of Sheets) together here is a great solution that I just published on my YouTube channel

https://www.youtube.com/watch?v=kVA8K4QZvYw

Hope that helps

Nabil Mourad

This is a Bite-Size Excel Tutorial Series (less than 3 minutes) targeting anyone on the globe using Excel since I broke the barrier of language and I explain the topic fully in graphics. Consolidating Multiple Sheets into one single summary sheet is a common task in Excel. In this "Capsule" I show
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies