Need a table based on DATE... but its complicated

indiemusicboy

New Member
Joined
Apr 9, 2020
Messages
30
Office Version
  1. 365
Platform
  1. 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:

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]
Screen Shot 2021-03-30 at 10.01.30 AM.png


VIEW OF SUMMARY: Example of the field I want populated in the "Dashboard" tab ranked based on data per customer worksheet. [image below]
Screen Shot 2021-03-30 at 10.05.15 AM.png


VIEW OF CUSTOMER WORKSHEET SOURCE DATA: The data it is extracting from looks like this on each customer tab. [image below]
Screen Shot 2021-03-30 at 10.23.48 AM.png


VIEW OF "INDEX" WORKSHEET:
Screen Shot 2021-03-30 at 10.32.23 AM.png
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

indiemusicboy

New Member
Joined
Apr 9, 2020
Messages
30
Office Version
  1. 365
Platform
  1. MacOS
Happy to explain further if it helps clarify the ask. I understand that I'm not the best at relaying the flow at times.
 

indiemusicboy

New Member
Joined
Apr 9, 2020
Messages
30
Office Version
  1. 365
Platform
  1. MacOS
I wonder if I can somehow create a formula in column Q that lists dates within 90 days of TODAY from the INDEX worksheet - which will populate dates in Q52 thru Q67 (one each) - I can then use OFFSET to then grab the corresponding data like Customer name, service, etc. from cells in that same row within INDEX too?

So Q52 populates a date if it finds one that is within 3-months-from-today (from INDEX), and then I use OFFSET to pull its corresponding data from that date's row (from INDEX), to populate matching data for that date's related service, value, CSM, Health, etc?

-or-

Is it more like using an INDEX+MATCH function to do all at the same time?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,335
Messages
5,641,557
Members
417,220
Latest member
lam150498

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top