Excel Changing returned value based off date

[GLT07]

I have a excel workbook that is filled out daily that I am attempting to make a dashboard out of. One of the challenges I came across is one of my clients change locations or management a lot. I decided to build a "Settings" sheet to document where that client is, what the building is called, client name, the manager, senior manager, and the date the "setting" was changed.

On another sheet I collect all the data from all the clients inputs for each day. What I am trying to do is index match to get the site,building, manager, and senior manager. The issue I am hitting is when the setting is changed, how do I return the correct data for the correct date. Since I have running data it will need to return the same site,building, manager,senior manager until the date matches the setting change date then use that data until the setting is changed again.



Settings

Site	Building	Client	Manager	Senior Manager	Setting Changed
Sample Site 1	B1	Client 1	Brandy Doe	John Doe	4/1/2018
Sample Site 1	B2	Client 2	Cindy Doe	Bob Doe	4/1/2018
Sample Site 2	B3	Client 3	Tim Doe	Jane Doe	4/1/2018
Sample Site 2	B3	Client 4	Tim Doe	Jane Doe	4/1/2018
Sample Site 2	B3	Client 1	Brandy Doe	John Doe	5/2/2018
Sample Site 2	B3	Client 1	Time Doe	Jane Doe	5/8/2018

<tbody>
</tbody>

In my example, client 1 changed sites and building on 5/2 and then on 5/8 changes management team.

 

[Peter_SSs]

Welcome to the MrExcel board!

OK, so you have shown us what the settings sheet looks like, now can you show a small section of the other sheet and what you want returned? The sample should at least include something about Client 1 so we can see just what you are trying to achieve in relation to dates.

 

[GLT07]

Sorry it took awhile for the response, Below I changed some of the settings on the settings sheet to better represent what I am attempting on the Aggregate sheet.

Settings
Changing Dates from original post too better represent what I am attempting below​

Site	Building	Client	Manager	Senior Manager	Setting Changed
Sample Site 1	B1	Client 1	Brandy Doe	John Doe	4/1/2018
Sample Site 1	B2	Client 2	Cindy Doe	Bob Doe	4/1/2018
Sample Site 2	B3	Client 3	Tim Doe	Jane Doe	4/1/2018
Sample Site 2	B3	Client 4	Tim Doe	Jane Doe	4/1/2018
Sample Site 2	B3	Client 1	Brandy Doe	John Doe	4/3/2018
Sample Site 2	B3	Client 1	Tim Doe	Jane Doe	4/5/2018

<tbody>
</tbody>

Aggregate Sheet
This sheet combines data from many different sheets but needs to compensate for when clients move or management changes for the clients which is all document on the settings page and the date the change occurred. I have the date on this sheet expanded down to the end of 2019 with formulas for the data expanded down also but returning "" if there is an error. This was when the manual entry sheets are updated with information the aggregate sheet is automatically updated.
(Text in Red comes from several other sheets that is manual entry)
(Text in Blue comes from the setting sheet)
​

Date	Client	Site	Building	Senior Manager	Manager	Errors From Audit	Orders Audited	Total Trouble Orders	Shipped Orders
4/1/2018	Client 1	Sample Site 1	B1	John Doe	Brandy Doe	2	420	16	920
4/1/2018	Client 2	Sample Site 1	B2	Bob Doe	Cindy Doe	1	100	5	630
4/1/2018	Client 3	Sample Site 2	B3	Jane Doe	Tim Doe	14	1000	82	3500
4/1/2018	Client 4	Sample Site 2	B3	Jane Doe	Tim Doe	0	20	1	300
4/2/2018	Client 1	Sample Site 1	B1	John Doe	Brandy Doe	0	400	15	950
4/2/2018	Client 2	Sample Site 1	B2	Bob Doe	Cindy Doe	1	120	2	620
4/2/2018	Client 3	Sample Site 2	B3	Jane Doe	Tim Doe	11	980	65	2800
4/2/2018	Client 4	Sample Site 2	B3	Jane Doe	Tim Doe	0	18	0	280
4/3/2018	Client 1	Sample Site 2	B3	John Doe	Brandy Doe	1	411	15	911
4/3/2018	Client 2	Sample Site 1	B2	Bob Doe	Cindy Doe	0	140	1	560
4/3/2018	Client 3	Sample Site 2	B3	Jane Doe	Tim Doe	14	1100	72	3800
4/3/2018	Client 4	Sample Site 2	B3	Jane Doe	Tim Doe	0	17	0	180
4/4/2018	Client 1	Sample Site 2	B3	John Doe	Brandy Doe	0	420	16	960
4/4/2018	Client 2	Sample Site 1	B2	Bob Doe	Cindy Doe	0	130	0	490
4/4/2018	Client 3	Sample Site 2	B3	Jane Doe	Tim Doe	10	900	45	1600
4/4/2018	Client 4	Sample Site 2	B3	Jane Doe	Tim Doe	1	10	0	90
4/5/2018	Client 1	Sample Site 2	B3	Jane Doe	Tim Doe	0	370	14	900
4/5/2018	Client 2	Sample Site 1	B2	Bob Doe	Cindy Doe	0	120	0	470
4/5/2018	Client 3	Sample Site 2	B3	Jane Doe	Tim Doe	9	800	32	1400
4/5/2018	Client 4	Sample Site 2	B3	Jane Doe	Tim Doe	0	5	0	30

<tbody>
</tbody>

If needed, I can provide the formula's I use to get all the data on the aggregate sheet. Ultimately with the aggregate sheet I create a dashboard (graphs, etc) and "pretty" it up for the management team. Again, what I am trying to figure out is a formula to change the data I am getting on the aggregate sheet when it is change on the settings page but not until it reaches the date set on the settings page and continues pulling that data until a new setting is set with a new date.

 

[Peter_SSs]

Thanks, that's much clearer.

I would use a helper column in the Aggregate sheet. I have used column K and that column could be hidden once the formulas have been populated in it.

Assuming the sample data in 'Settings' is in A1:F7 (You will have to adjust the ranges in your actual sheet) then try each of these, copied down.

Excel Workbook
	A	B	C	D	E	F	K
1	Date	Client	Site	Building	Senior Manager	Manager	Indx
2	4/01/2018	Client 1	Sample Site 1	B1	John Doe	Brandy Doe	1
3	4/01/2018	Client 2	Sample Site 1	B2	Bob Doe	Cindy Doe	2
4	4/01/2018	Client 3	Sample Site 2	B3	Jane Doe	Tim Doe	3
5	4/01/2018	Client 4	Sample Site 2	B3	Jane Doe	Tim Doe	4
6	4/02/2018	Client 1	Sample Site 1	B1	John Doe	Brandy Doe	1
7	4/02/2018	Client 2	Sample Site 1	B2	Bob Doe	Cindy Doe	2
8	4/02/2018	Client 3	Sample Site 2	B3	Jane Doe	Tim Doe	3
9	4/02/2018	Client 4	Sample Site 2	B3	Jane Doe	Tim Doe	4
10	4/03/2018	Client 1	Sample Site 2	B3	John Doe	Brandy Doe	5
11	4/03/2018	Client 2	Sample Site 1	B2	Bob Doe	Cindy Doe	2
12	4/03/2018	Client 3	Sample Site 2	B3	Jane Doe	Tim Doe	3
13	4/03/2018	Client 4	Sample Site 2	B3	Jane Doe	Tim Doe	4
14	4/04/2018	Client 1	Sample Site 2	B3	John Doe	Brandy Doe	5
15	4/04/2018	Client 2	Sample Site 1	B2	Bob Doe	Cindy Doe	2
16	4/04/2018	Client 3	Sample Site 2	B3	Jane Doe	Tim Doe	3
17	4/04/2018	Client 4	Sample Site 2	B3	Jane Doe	Tim Doe	4
18	4/05/2018	Client 1	Sample Site 2	B3	Jane Doe	Tim Doe	6
19	4/05/2018	Client 2	Sample Site 1	B2	Bob Doe	Cindy Doe	2
20	4/05/2018	Client 3	Sample Site 2	B3	Jane Doe	Tim Doe	3
21	4/05/2018	Client 4	Sample Site 2	B3	Jane Doe	Tim Doe	4
Aggregate