Link to home
Start Free TrialLog in
Avatar of Mike Caldwell
Mike CaldwellFlag for United States of America

asked on

Need an Excel cell value to freeze once entered

Mr. Google can't find this, but maybe I just don't know the term to search for.  

I have a spreadsheet that tracks transactions in various currencies.  The exchange values change daily.  I have a cell with today's value, and when I make a deal the record for that deal records the USD equivalent of it based upon the cell value.  But if the exchange rate changes tomorrow, the deal record will update.  Instead of doing the math and entering a value, I would like the deal record to revert to just a value instead of a formula or somehow freeze once the other parameters of the deal are entered.  I'm hoping for something that does not involve Visual Basic.
Avatar of Ganesh Anand
Ganesh Anand
Flag of Bahrain image

Hello, Please post your excel sheet sample to see if anything can be done.

But reading your requirement seems, you need VBA to perform the special operation you are looking for, there is a condition but you cannot do with what you want without VBA.
Avatar of Mike Caldwell

ASKER

What about a macro, to copy and then paste a value?  I wouldn't mind that, just not very confident in Visual Basic.  I have written a lot of stuff in VB Script, but not "real" VB.
Sample attached.  The issue is that the Euro/USD exchange rate changes daily.  Actually minute by minute, but I just use the value I have at the start of a day.  Just want to enter the Euro amount and it converts to USD using the daily rate, but on the sample the historical values would change too.
-EE-Sample.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I agree with Patrick's suggestion and was going to suggest the same.

Another advantage with this is that you also then have a record of the daily exchange rates and can use for historical tracking or even maybe forward planning.
Makes sense.  What I am doing is not related to Euro but something else that may not change for a few days, but I can fill the table with dates for a year, and a formula for values that take the value of the previous day, and then force a value for the day it changes.
With a VLOOKUP, if you set the fourth parameter to TRUE and sort the exchange rates in ascending order by date (oldest at the top, most recent at the bottom), the search down the date column will stop at the last date that is not greater than the date being looked for. For example, if you had an exchange rate for 21 Nov and the exchange rate changes on 25 Nov and a deal dated 22 Nov, when looking for 22 Nov it would stop at 21 Nov and use that exchange rate.

You don't need to populate the rates for 22, 23 & 24 Nov for it to work.

Would this be the correct action in your scenario?
Shoulda thought of it myself!  Thanks for the nudge.