Excel Changing returned value based off date

G

GLT07

New Member
Joined
Oct 8, 2018
Messages
2
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
[TABLE="width: 500"]
<tbody>[TR]
[TD]Site[/TD]
[TD]Building[/TD]
[TD]Client[/TD]
[TD]Manager[/TD]
[TD]Senior Manager[/TD]
[TD]Setting Changed[/TD]
[/TR]
[TR]
[TD]Sample Site 1[/TD]
[TD]B1[/TD]
[TD]Client 1[/TD]
[TD]Brandy Doe[/TD]
[TD]John Doe[/TD]
[TD]4/1/2018[/TD]
[/TR]
[TR]
[TD]Sample Site 1[/TD]
[TD]B2[/TD]
[TD]Client 2[/TD]
[TD]Cindy Doe[/TD]
[TD]Bob Doe[/TD]
[TD]4/1/2018[/TD]
[/TR]
[TR]
[TD]Sample Site 2[/TD]
[TD]B3[/TD]
[TD]Client 3[/TD]
[TD]Tim Doe[/TD]
[TD]Jane Doe[/TD]
[TD]4/1/2018[/TD]
[/TR]
[TR]
[TD]Sample Site 2[/TD]
[TD]B3[/TD]
[TD]Client 4[/TD]
[TD]Tim Doe[/TD]
[TD]Jane Doe[/TD]
[TD]4/1/2018[/TD]
[/TR]
[TR]
[TD]Sample Site 2[/TD]
[TD]B3[/TD]
[TD]Client 1[/TD]
[TD]Brandy Doe[/TD]
[TD]John Doe[/TD]
[TD]5/2/2018[/TD]
[/TR]
[TR]
[TD]Sample Site 2[/TD]
[TD]B3[/TD]
[TD]Client 1[/TD]
[TD]Time Doe[/TD]
[TD]Jane Doe[/TD]
[TD]5/8/2018[/TD]
[/TR]
</tbody>[/TABLE]


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

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
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.
 
Last edited:
Upvote 0
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​
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Site
[/TD]
[TD]Building
[/TD]
[TD]Client
[/TD]
[TD]Manager
[/TD]
[TD]Senior Manager
[/TD]
[TD]Setting Changed
[/TD]
[/TR]
[TR]
[TD]Sample Site 1
[/TD]
[TD]B1
[/TD]
[TD]Client 1
[/TD]
[TD]Brandy Doe
[/TD]
[TD]John Doe
[/TD]
[TD]4/1/2018
[/TD]
[/TR]
[TR]
[TD]Sample Site 1
[/TD]
[TD]B2
[/TD]
[TD]Client 2
[/TD]
[TD]Cindy Doe
[/TD]
[TD]Bob Doe
[/TD]
[TD]4/1/2018
[/TD]
[/TR]
[TR]
[TD]Sample Site 2
[/TD]
[TD]B3
[/TD]
[TD]Client 3
[/TD]
[TD]Tim Doe
[/TD]
[TD]Jane Doe
[/TD]
[TD]4/1/2018
[/TD]
[/TR]
[TR]
[TD]Sample Site 2
[/TD]
[TD]B3
[/TD]
[TD]Client 4
[/TD]
[TD]Tim Doe
[/TD]
[TD]Jane Doe
[/TD]
[TD]4/1/2018
[/TD]
[/TR]
[TR]
[TD]Sample Site 2
[/TD]
[TD]B3
[/TD]
[TD]Client 1
[/TD]
[TD]Brandy Doe
[/TD]
[TD]John Doe
[/TD]
[TD]4/3/2018
[/TD]
[/TR]
[TR]
[TD]Sample Site 2
[/TD]
[TD]B3
[/TD]
[TD]Client 1
[/TD]
[TD]Tim Doe
[/TD]
[TD]Jane Doe
[/TD]
[TD]4/5/2018
[/TD]
[/TR]
</tbody>[/TABLE]



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)
​
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Client
[/TD]
[TD]Site
[/TD]
[TD]Building
[/TD]
[TD]Senior Manager
[/TD]
[TD]Manager
[/TD]
[TD]Errors From Audit
[/TD]
[TD]Orders Audited
[/TD]
[TD]Total Trouble Orders
[/TD]
[TD]Shipped Orders
[/TD]
[/TR]
[TR]
[TD]4/1/2018
[/TD]
[TD]Client 1
[/TD]
[TD]Sample Site 1
[/TD]
[TD]B1
[/TD]
[TD]John Doe
[/TD]
[TD]Brandy Doe
[/TD]
[TD]2
[/TD]
[TD]420
[/TD]
[TD]16
[/TD]
[TD]920
[/TD]
[/TR]
[TR]
[TD]4/1/2018
[/TD]
[TD]Client 2
[/TD]
[TD]Sample Site 1
[/TD]
[TD]B2
[/TD]
[TD]Bob Doe
[/TD]
[TD]Cindy Doe
[/TD]
[TD]1
[/TD]
[TD]100
[/TD]
[TD]5
[/TD]
[TD]630
[/TD]
[/TR]
[TR]
[TD]4/1/2018
[/TD]
[TD]Client 3
[/TD]
[TD]Sample Site 2
[/TD]
[TD]B3
[/TD]
[TD]Jane Doe
[/TD]
[TD]Tim Doe
[/TD]
[TD]14
[/TD]
[TD]1000
[/TD]
[TD]82
[/TD]
[TD]3500
[/TD]
[/TR]
[TR]
[TD]4/1/2018
[/TD]
[TD]Client 4
[/TD]
[TD]Sample Site 2
[/TD]
[TD]B3
[/TD]
[TD]Jane Doe
[/TD]
[TD]Tim Doe
[/TD]
[TD]0
[/TD]
[TD]20
[/TD]
[TD]1
[/TD]
[TD]300
[/TD]
[/TR]
[TR]
[TD]4/2/2018
[/TD]
[TD]Client 1
[/TD]
[TD]Sample Site 1
[/TD]
[TD]B1
[/TD]
[TD]John Doe
[/TD]
[TD]Brandy Doe
[/TD]
[TD]0
[/TD]
[TD]400
[/TD]
[TD]15
[/TD]
[TD]950
[/TD]
[/TR]
[TR]
[TD]4/2/2018
[/TD]
[TD]Client 2
[/TD]
[TD]Sample Site 1
[/TD]
[TD]B2
[/TD]
[TD]Bob Doe
[/TD]
[TD]Cindy Doe
[/TD]
[TD]1
[/TD]
[TD]120
[/TD]
[TD]2
[/TD]
[TD]620
[/TD]
[/TR]
[TR]
[TD]4/2/2018
[/TD]
[TD]Client 3
[/TD]
[TD]Sample Site 2
[/TD]
[TD]B3
[/TD]
[TD]Jane Doe
[/TD]
[TD]Tim Doe
[/TD]
[TD]11
[/TD]
[TD]980
[/TD]
[TD]65
[/TD]
[TD]2800
[/TD]
[/TR]
[TR]
[TD]4/2/2018
[/TD]
[TD]Client 4
[/TD]
[TD]Sample Site 2
[/TD]
[TD]B3
[/TD]
[TD]Jane Doe
[/TD]
[TD]Tim Doe
[/TD]
[TD]0
[/TD]
[TD]18
[/TD]
[TD]0
[/TD]
[TD]280
[/TD]
[/TR]
[TR]
[TD]4/3/2018
[/TD]
[TD]Client 1
[/TD]
[TD]Sample Site 2
[/TD]
[TD]B3
[/TD]
[TD]John Doe
[/TD]
[TD]Brandy Doe
[/TD]
[TD]1
[/TD]
[TD]411
[/TD]
[TD]15
[/TD]
[TD]911
[/TD]
[/TR]
[TR]
[TD]4/3/2018
[/TD]
[TD]Client 2
[/TD]
[TD]Sample Site 1
[/TD]
[TD]B2
[/TD]
[TD]Bob Doe
[/TD]
[TD]Cindy Doe
[/TD]
[TD]0
[/TD]
[TD]140
[/TD]
[TD]1
[/TD]
[TD]560
[/TD]
[/TR]
[TR]
[TD]4/3/2018
[/TD]
[TD]Client 3
[/TD]
[TD]Sample Site 2
[/TD]
[TD]B3
[/TD]
[TD]Jane Doe
[/TD]
[TD]Tim Doe
[/TD]
[TD]14
[/TD]
[TD]1100
[/TD]
[TD]72
[/TD]
[TD]3800
[/TD]
[/TR]
[TR]
[TD]4/3/2018
[/TD]
[TD]Client 4
[/TD]
[TD]Sample Site 2
[/TD]
[TD]B3
[/TD]
[TD]Jane Doe
[/TD]
[TD]Tim Doe
[/TD]
[TD]0
[/TD]
[TD]17
[/TD]
[TD]0
[/TD]
[TD]180
[/TD]
[/TR]
[TR]
[TD]4/4/2018
[/TD]
[TD]Client 1
[/TD]
[TD]Sample Site 2
[/TD]
[TD]B3
[/TD]
[TD]John Doe
[/TD]
[TD]Brandy Doe
[/TD]
[TD]0
[/TD]
[TD]420
[/TD]
[TD]16
[/TD]
[TD]960
[/TD]
[/TR]
[TR]
[TD]4/4/2018
[/TD]
[TD]Client 2
[/TD]
[TD]Sample Site 1
[/TD]
[TD]B2
[/TD]
[TD]Bob Doe
[/TD]
[TD]Cindy Doe
[/TD]
[TD]0
[/TD]
[TD]130
[/TD]
[TD]0
[/TD]
[TD]490
[/TD]
[/TR]
[TR]
[TD]4/4/2018
[/TD]
[TD]Client 3
[/TD]
[TD]Sample Site 2
[/TD]
[TD]B3
[/TD]
[TD]Jane Doe
[/TD]
[TD]Tim Doe
[/TD]
[TD]10
[/TD]
[TD]900
[/TD]
[TD]45
[/TD]
[TD]1600
[/TD]
[/TR]
[TR]
[TD]4/4/2018
[/TD]
[TD]Client 4
[/TD]
[TD]Sample Site 2
[/TD]
[TD]B3
[/TD]
[TD]Jane Doe
[/TD]
[TD]Tim Doe
[/TD]
[TD]1
[/TD]
[TD]10
[/TD]
[TD]0
[/TD]
[TD]90
[/TD]
[/TR]
[TR]
[TD]4/5/2018
[/TD]
[TD]Client 1
[/TD]
[TD]Sample Site 2
[/TD]
[TD]B3
[/TD]
[TD]Jane Doe
[/TD]
[TD]Tim Doe
[/TD]
[TD]0
[/TD]
[TD]370
[/TD]
[TD]14
[/TD]
[TD]900
[/TD]
[/TR]
[TR]
[TD]4/5/2018
[/TD]
[TD]Client 2
[/TD]
[TD]Sample Site 1
[/TD]
[TD]B2
[/TD]
[TD]Bob Doe
[/TD]
[TD]Cindy Doe
[/TD]
[TD]0
[/TD]
[TD]120
[/TD]
[TD]0
[/TD]
[TD]470
[/TD]
[/TR]
[TR]
[TD]4/5/2018
[/TD]
[TD]Client 3
[/TD]
[TD]Sample Site 2
[/TD]
[TD]B3
[/TD]
[TD]Jane Doe
[/TD]
[TD]Tim Doe
[/TD]
[TD]9
[/TD]
[TD]800
[/TD]
[TD]32
[/TD]
[TD]1400
[/TD]
[/TR]
[TR]
[TD]4/5/2018
[/TD]
[TD]Client 4
[/TD]
[TD]Sample Site 2
[/TD]
[TD]B3
[/TD]
[TD]Jane Doe
[/TD]
[TD]Tim Doe
[/TD]
[TD]0
[/TD]
[TD]5
[/TD]
[TD]0
[/TD]
[TD]30
[/TD]
[/TR]
</tbody>[/TABLE]



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.
 
Upvote 0
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
ABCDEFK
1DateClientSiteBuildingSenior ManagerManagerIndx
24/01/2018Client 1Sample Site 1B1John DoeBrandy Doe1
34/01/2018Client 2Sample Site 1B2Bob DoeCindy Doe2
44/01/2018Client 3Sample Site 2B3Jane DoeTim Doe3
54/01/2018Client 4Sample Site 2B3Jane DoeTim Doe4
64/02/2018Client 1Sample Site 1B1John DoeBrandy Doe1
74/02/2018Client 2Sample Site 1B2Bob DoeCindy Doe2
84/02/2018Client 3Sample Site 2B3Jane DoeTim Doe3
94/02/2018Client 4Sample Site 2B3Jane DoeTim Doe4
104/03/2018Client 1Sample Site 2B3John DoeBrandy Doe5
114/03/2018Client 2Sample Site 1B2Bob DoeCindy Doe2
124/03/2018Client 3Sample Site 2B3Jane DoeTim Doe3
134/03/2018Client 4Sample Site 2B3Jane DoeTim Doe4
144/04/2018Client 1Sample Site 2B3John DoeBrandy Doe5
154/04/2018Client 2Sample Site 1B2Bob DoeCindy Doe2
164/04/2018Client 3Sample Site 2B3Jane DoeTim Doe3
174/04/2018Client 4Sample Site 2B3Jane DoeTim Doe4
184/05/2018Client 1Sample Site 2B3Jane DoeTim Doe6
194/05/2018Client 2Sample Site 1B2Bob DoeCindy Doe2
204/05/2018Client 3Sample Site 2B3Jane DoeTim Doe3
214/05/2018Client 4Sample Site 2B3Jane DoeTim Doe4
Aggregate
 
Upvote 0
You must log in or register to reply here.

Similar threads

HansM
  • Question Question
Looking for a specific number in a cell that can have multiple numbers separated by a comma (Using Mac Excel)
Replies
5
Views
296
Jeffrey Mahoney
Jeffrey Mahoney
S
  • Question Question
Pulling Correct Number of Dates Based on Column Value
Replies
1
Views
41
dreid1011
dreid1011
A
  • Question Question
Automate Attendance Marking Based on Daily Schedule in Excel
Replies
1
Views
195
Peter_SSs
Peter_SSs
E
  • Question Question
Generate unique code based on user form entry and put in table
2
Replies
18
Views
488
Micron
M
S
  • Question Question
VBA : Reflection Workspace extract all screen/pages
Replies
0
Views
101
Samantha27
S

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

Share this page

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
Back
Top