Pull Data from Specific Cells based on Date

[JonComeau]

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]

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

---

[#3]

@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

[#4]

@JonComeauI have a similar problem. If you were able to solve this, let me know.