From how you describe this I would either:

Add a new column with the FX Rate to use, then the formula multiplies the
new column. Then all of the logic can go into the FX Rate To Use formula and
not in the key metric.

Extend the key metric to include all of the possible FX Rates. Make the
formula more sophisticated so that you can fill it down for new rows and it
will work without find/replace.


--
Tim Zych
www.higherdata.com
Compare data in worksheets and find differences with Workbook Compare
A free, powerful, flexible Excel utility


"Maury Markowitz" wrote in message
...
Bear with me here...

I have a sheet that is several thousand lines long with all the
inventory held in a particular bank account. Several of the columns
are formulas that contain an FX rate, and which rate to use depends on
the currency on the current line compared to the account.

If the currency is the same as the bank account's, I can just replace
the rate with "1". If it's a CAD holding in a USD account, then I want
to replace it with "0.97". If it's USD holding in a CAD account, I
want to use "1.03".

I've found that Excel seems to be much faster if you apply a single
formula to a range (column-by-column in this case) than looping over
the rows and putting a formula in each one one-by-one (perhaps this is
not true?).

So what I thought is that I should put in a placeholder of some sort,
something in the formula that I can just search and replace. Like
this...

"=(E5*F5*BE5)*FXRATE"

But it can't just say "FXRATE", it has to say something like
"FXRATEUSD" so I know what number to replace it with. For the life of
me I can't figure out how to write that formula. It's just a string
concat, right?

Any advice? Or should I just punt and roll over the rows in a loop?

Maury