Using a sinlge "Variable" across spread sheet - Maybe simple

  • Thread starter Thread starter Dave Smithz
  • Start date Start date
D

Dave Smithz

Hi There,

I have a spreadsheet with a list of UK prices. I want to have next to that
the list of US prices at the current periods exchange rate.

A (Item) B (Uk Price) C (US Price)
Apple £2.90 $3.80
Banana £1.40 $3.80
... many more items in spread sheet.


Therefore using the above example I set up a simple form that multiplies
Column B by the exchange rate. I then drag that formula to all the columns
below.

However, I'm want to save time and have a single column that holds the
exchange rate that if I change, all the US prices on my spread sheet change.

Now I am aware that this is quite possible, but what I want to avoid is
having to manually set up each cell that holds a dollar amount to calculate
a product based on a value that is located in a single cell (The exchange
rate).
When I drag the formula (or copy and paste it to other cells where I want to
apply) it always looks for the exchange rate in a cell, an amount of cells
equal to the amount of cells between the cell I am copying from and the cell
I am pasting too.

If there was some way I could set up a "Variable" that I could actually use
in Formula. E.g. If I could set up in Excel that EXCHANGE_RATE = 1.6, and
used EXCHANGE_RATE in formulas, then I could copy and paste the formula much
easier.

I hope this makes sense and anyone who has tried this I'm sure will know
what I am talking about.

Kind regards

Dave
 
Hi Dave
you may define a name for this cell which holds the exchange rate (goto
'Insert - Name - Define') and use this in your calculation as kind of
'variable')
 
Hi Dave

if i understand correctly, i see two ways to approach what you're after

in a cell - say "D1" enter 1.6 (or whatever the current exchange rate is)

now in your formulas when you use cell D1 add it in like this
$D$1
when you drag it it won't change (the $ indicate that the reference is
absolute - you can either type the $ or use the F4 key when typing your
formula to add them in - type reference press F4)

OR

click on D1 and then click in the NAME BOX (little box to left of formula
bar)
type
EXCHANGE_RATE and press ENTER (pressing enter's important) (this is known
as a range name)

then you can use EXCHANGE_RATE in your formulas e.g.

=A1*EXCHANGE_RATE
and you don't even have to type it - pressing F3 will bring up a list of all
range names within the workbook.

Additionally, if you have used D1 previously in your formulas - once you
give it a name you can use Insert / Name / Apply to replace D1 in all your
formulas with EXCHANGE_RATE

range names are always absolute references so no need for $

Hope this helps

Cheers
JulieD
 
Dave

You're almost there!!
In an empty cell, type in the exchange rate and click in the Name box (to
the left of the formula bar) and type in a name for the cell (eg EXRATE).
Now you can use =EXRATE * 5 for example to use that figure.

Andy.
 
To all that replied.

AT LAST - absoulte references - what a god send. You do not know how much
time I have wasted copying formulas to others cells and then making manual
adjustments to the (I now know) relative references.

Why didn't I ask in this news group a long time ago.

Kind regards

Dave
 
Back
Top