indiemusicboy
New Member
- Joined
- Apr 9, 2020
- Messages
- 47
- Office Version
- 365
- Platform
- MacOS
I'm trying to create table or something dependent if the (renewal) date is upcoming. If that date is 3 months from today, it will list a series of information related to that date. So it ranks based on date but has to pull a lot of criteria across the worksheet associated to that date. Here is the criteria:
All of the above data points are captured in a workbook in which each tab is a collection of data for a specific customer. Those tabs are based on a template so each customer tab is formatted the exact same way/same cell locations. In other words, the "client name" is always going to be in F3. The services are always going to be in a range B138:B145. I noted those ranges/cells above in between the "[ ]"
I've created "Index" worksheet that pulls all the data per worksheet into an index view so I could create pivot tables.
My problem is that each client could have 1 service [B138] or multiple services [B138:B145]. Some of those services may have a renewal date coming up. Some may not. I only care for the series of data above IF the date is 3 months from TODAY.
Images are below to help you understand the layout a bit more...
VIEW OF TABS: I have a rollup or "dashboard" tab that summarizes key data points I want to pull from those subsequent client tabs. [image below]
VIEW OF SUMMARY: Example of the field I want populated in the "Dashboard" tab ranked based on data per customer worksheet. [image below]
VIEW OF CUSTOMER WORKSHEET SOURCE DATA: The data it is extracting from looks like this on each customer tab. [image below]
VIEW OF "INDEX" WORKSHEET:
1. Client name (single reference) [F3]
2. Service (multiple references) [B138:B145]
3. Renewal date (multiple references) [P138:P145]
4. Value (multiple references) [O138:O145]
5. The name of the account rep (single reference) [L3]
6. Health score (multiple references) [L138:L145]
7. CSAT (single references) [R10]
All of the above data points are captured in a workbook in which each tab is a collection of data for a specific customer. Those tabs are based on a template so each customer tab is formatted the exact same way/same cell locations. In other words, the "client name" is always going to be in F3. The services are always going to be in a range B138:B145. I noted those ranges/cells above in between the "[ ]"
I've created "Index" worksheet that pulls all the data per worksheet into an index view so I could create pivot tables.
My problem is that each client could have 1 service [B138] or multiple services [B138:B145]. Some of those services may have a renewal date coming up. Some may not. I only care for the series of data above IF the date is 3 months from TODAY.
Images are below to help you understand the layout a bit more...
VIEW OF TABS: I have a rollup or "dashboard" tab that summarizes key data points I want to pull from those subsequent client tabs. [image below]
VIEW OF SUMMARY: Example of the field I want populated in the "Dashboard" tab ranked based on data per customer worksheet. [image below]
VIEW OF CUSTOMER WORKSHEET SOURCE DATA: The data it is extracting from looks like this on each customer tab. [image below]
VIEW OF "INDEX" WORKSHEET: