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.
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.
