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

[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

 

[Frank Kabel]

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')

 

[JulieD]

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

 

[Andy B]

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.

 

[Dave Smithz]

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