Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 0 down vote accepted

There are several approaches to this.

If you don't want to copy the existing cells and paste them as values, you can introduce a new column that identifies which code should be used. Insert a new column before column A. For the historic values enter "A", for the new values enter "B". You can use a formula to ensure that each new row now receives the value B, for example if the last historical value was entered in row 524 and after that only new values should be looked up, use a formula like this:


(I don't know what IF() and ROW() are in your language, but I trust you can find that out.) You can then hide column A.

Then add a new column to the Pres data lookup table. In column E add the new values. In cell D4 enter "A", in E4 enter "B". Then you can change the lookup formula in the whole table

=INDEX('Pres data'!D$5:E$141;COMPARE(W524;'Pres data'!C$5:C$141;0);COMPARE(A524;'Pres data'!$D$4:$E$4;0))
share|improve this answer

I would recommend leaving the historical data and functions in place (perhaps renaming them reflect their status) and creating new tables and functions for the new data. This would allow someone to see how the historical data was calculated.

The other alternative would be to copy all of the historical data and using Paste Special paste only the values in a new worksheet. Then modify the formulas and use them for the new rows. This will preserve the old data but with values only.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.