Forum Discussion
Need key that is used to refer to a particular cell when copying formula in Excel spreadsheet
I need to compute costs in forehgn money using exchange rate in table. How do I avoid having to retype exchange rate for each cell with a formula? (what key do I use?)
- Wonderful
That is what I remember. I will be 80 in a couple of months and haven't used that feature in years, I was kicking myself for forgetting the trick. I normally do not resort to visiting a chat rooms because I might get lost there and have no idea what to expect as a result. Your help has made my day! My wife and have a 502(c)3 "Living Water for Kenya" (livingwaterforKenya.org) which requires our attention via internet daily. I work alot with spreadsheets and this will help. We have been to Kenya 19 times since 2005. Thanks again David Hansen Email address removed.
6 Replies
Did you mean selecting a cell or range reference in a formula, then pressing F4 to make the reference absolute?
- Thank you that may be what I was looking for ... I seem to recall it being the embedment of a $ signbefore the cell reference but had not succeeded that way yet. your suggest is similar; I shall retry both.
Using the $ is useful to lock down to a cell for copying formulas e.g a formula referencing $A1 will repeat to A1,A2,A3 etc when dragged down, but hold to A1 when dragged across. The inverse is true if you use A$1. To lock in Both directions you would use $A$1. As mentioned above using the F4 key in the formula can do this.
Named cells/ranges are much more useful when referring to cells on a different worksheet. i.e. you may have all of your conversion rates on one worksheet, and your formulas and data on another. Named ranges are also great when using Vlookup etc, as the ensure accuracy is maintained.
dw-hansen Take a look at the range name function. You can name a cell (e.g. the USD - GBP exchange rate) as USGBP and then refer to USGBP in your formulas. See more here https://support.microsoft.com/en-us/office/define-and-use-names-in-formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64
- Although that wasn't exactly what I recall one uses to hold constant a part of a formula as the formula moves arround, It did the job well.
