Jun 29 2019 12:36 AM - edited Jun 29 2019 12:37 AM
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.
Jun 30 2019 05:28 AM
@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.
Jun 30 2019 05:05 PM
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
Jun 30 2021 09:46 AM
@JonComeauI have a similar problem. If you were able to solve this, let me know.