Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Need an Excel cell value to freeze once entered

Posted on 2016-11-21
Medium Priority
Last Modified: 2016-11-25
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.
Question by:Mike Caldwell
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 12

Expert Comment

by:Ganesh Kumar A
ID: 41896367
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.

Author Comment

by:Mike Caldwell
ID: 41896400
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.

Author Comment

by:Mike Caldwell
ID: 41896440
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.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 41896500
If you only ever use one rate for each day, I recommend keeping a separate table of exchange rates.  Please see the attached file.

1) Added worksheet "FX Rates" with a table, FXRates, with two columns: Date and USD per Euro.  The rates in there now are FAKE. I made them up for the sake of example.  Replace them with real rates

2) Add a new row at the bottom of that table for each new day, and that day's exchange rate

3) On the Deals worksheet, use VLOOKUP to retrieve the exchange rate applicable for that day:

Note that because I omitted the fourth argument for VLOOKUP, you must ensure that the lookup table is sorted ascending by date.

For more info about VLOOKUP, you can see my article on the subject here.
LVL 33

Expert Comment

by:Rob Henson
ID: 41897243
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.

Author Comment

by:Mike Caldwell
ID: 41897561
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.
LVL 33

Expert Comment

by:Rob Henson
ID: 41897579
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?

Author Closing Comment

by:Mike Caldwell
ID: 41902030
Shoulda thought of it myself!  Thanks for the nudge.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question