I have a large amount of data in an excel sheet, which users type in directly, adding new lines with new data. Each new line of data represents a customer. Each customer will has a cell with: county, leader, price and department.
What the users to when adding new data is first to choose a county, based on the county they will have a dropdown list with different leaders who operate within this county for the leader cell.
Up until now the column of price and department have been filled by the piece of excel "code" below. The code just takes the value of leader, looks him up in a table and finds the line in the table where the leader is placed and returns the line number to the index function which can then pick out the appropriate price and department.
INDEX('Pres data'!D$5:D$141;COMPARE(W524;'Pres data'!C$5:C$141;0)))
Now however, a major rearrangement of departments have been done and if update the table all historical values will be incorrect and since the values are generated by the code, I can't just delete the code from the clients who should not be altered.
I could copy the current values of the department cell and manually paste it into clients who should not be changed, but this does not seem like an optimal solution.
Hope you are able to understand the setting. :) I have no idea of how to handle the problem, and do believe it is primarily a design fault of our own.
So any ideas of how to handle historical values in excel would also be nice.