amandabstewart
New Member
- Joined
- Aug 4, 2014
- Messages
- 45
My workbook uses both vlookup (price lists) and macros that update a log when I run it.
For example, cost of item A is $10 currently. Using vlookup, prices from the price sheet are entered automatically on a sales sheet. I run the macro that takes that sale info and copies parts of the row to a worksheet for who made the sale—eg Alex’s page would now say $10.
The problem is that if we change the prices on the price sheet and re-run the macro periodically to update new sales, eg cost of item A is changing to $15, it automatically changes the OLD $10 sale to the new value and Alex’s old sales would read $15. I don’t want it to overwrite the old data when I update the price sheet.
Is there a way to either:
- Make excel leave the old vlookup value in the sales sheet once a sale is made and NOT update the price on later sales (doubt this is possible)
- OR, make the macro not overwrite old data.
I really need it to leave the sales page alone too, and my old option was to have the world’s longest nested IF statement. That won’t work for the people who will use this workbook…they are basic users w/o ability to change something that complicated.
How can I make the sales entry page be “final’?
For example, cost of item A is $10 currently. Using vlookup, prices from the price sheet are entered automatically on a sales sheet. I run the macro that takes that sale info and copies parts of the row to a worksheet for who made the sale—eg Alex’s page would now say $10.
The problem is that if we change the prices on the price sheet and re-run the macro periodically to update new sales, eg cost of item A is changing to $15, it automatically changes the OLD $10 sale to the new value and Alex’s old sales would read $15. I don’t want it to overwrite the old data when I update the price sheet.
Is there a way to either:
- Make excel leave the old vlookup value in the sales sheet once a sale is made and NOT update the price on later sales (doubt this is possible)
- OR, make the macro not overwrite old data.
I really need it to leave the sales page alone too, and my old option was to have the world’s longest nested IF statement. That won’t work for the people who will use this workbook…they are basic users w/o ability to change something that complicated.
How can I make the sales entry page be “final’?