Excel 2007 - Keeping Historical Data

[Lauriston]

I have an Excel 2007 spreadsheet that captures all of our sales orders. I've been capturing this data since April 2012. One of the columns captures the Sales Representative (SR) initials based on the vendor purchasing the product. I have a current formula that performs a VLookup to a list of Vendors and the SR assigned to them and the data auto populates. Since April 2012 the SR’s assignments have never changed. However, as of October 1, 2013 the SR’s Vendor assignments are changing. BUT, I need to make sure that I keep historical data before I reassign the SR to their new vendors. So, I have to write a formula that changes the SR’s as of October 1, 2013 but keeps the current data (SR assignment) as is.

Additionally, the SR’s are going to be in a review period and the Brand assignments will change periodically based on performance or as SR’s leave and new SR’s replace them, so I also need to be able to include in the new formula a way to always retain current data but change as the assignments change.

I hope I am being clear. I'm not a an excel novice but I'm not an expert either. Thanks for any information you can provide.

 

[shift-del]

Hello

You need to include the date.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H
1	Date	Vendor	Sales Rep.			Vendor	Date	Sales Rep.
2	05.04.2012	ABC	Steve			ABC	01.01.2012	Steve
3	05.04.2012	DEF	Walter			ABC	01.10.2013	Max
4	05.04.2014	ABC	Max			DEF	01.01.2012	Walter
5	05.04.2014	DEF	Fred			DEF	01.10.2013	Fred

<tbody>
</tbody>

Zelle	Formel
C2	=LOOKUP(42,1/($F$2:$F$5=B2)/($G$2:$G$5<=A2),$H$2:$H$5)

<tbody>
</tbody>

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>

 

[Lauriston]

Good Morning and thanks for your response to my request. I will work on this today