How can I ensure prices stay the same if I update a spreadsheet?

Q I am a treasurer of a club and use an Excel spreadsheet to record sales and values of items sold. I set up a separate worksheet containing a price table and then used the VLOOKUP function to obtain the cost of each item.

This was working well until I changed the price of an item, which caused the sale value to change retrospectively for all transactions involving that item. How can I cure this problem?
Brian King

A If you want to solve this in an automated fashion then you are out of luck – at least, if you were hoping for an easy answer, that is.

The trouble is that VLOOKUP, in common with many of Excel’s built-in functions, can only take automation so far. Specifically, VLOOKUP references a particular cell and returns the value it contains.

If you later alter the value of the referenced cell – a price in this case – then the change will affect all the other cells that continue to use the same VLOOKUP-laden formula to check the price.

Essentially, you have a couple of options. The first is to remove the cost-calculating formula from older transactions before making price changes, meaning that the values in cells will remain fixed. The easiest way to do this en masse is to first select and copy the affected cells (left-click, drag to select, right-click and choose Copy).

Then, leaving the same cells selected, right-click again and choose Paste Special. When the Paste Special dialogue box appears, click to select the Values radio button and click OK.

The second option would be to create a new, separate lookup table each time the price list needs to change. This could be a sensible strategy anyway for accounting purposes, as it would allow you to track historical price movements.

Unless the lookup table is large and complex, it’s hardly a big effort. To do this, click and drag to select the existing lookup table, then right-click the selection and choose Copy.

Now right-click in a blank cell to the right or below (as per your preference) and choose Paste. Update the prices as necessary and then amend the VLOOKUP-bearing formula in the main worksheet to reference the cells containing the price in this new lookup table.

Finally, if you are intent on maintaining a single lookup table but would like to automate the process of fixing the prices in cells that represent older transactions, then we can envisage that it would be possible to achieve the task by writing a Visual Basic script – but that is well beyond the scope of No Problem.