Have you considered using data validation for the currency choice selection
(3rd cell) and a VLOOKUP() to get the rate in the 4th?

"Dom1966" wrote:

Hi,

I'm building a spreadsheet and I cannot resolve a problem.

It's basically designed to calculate costs. The first cell allows people to
type in an item. The next cell asks them to how many. The next, to choose a
currency from a drop down box. The 4th cell returns the exchange rate based
on the choice made in the drop down box. The 5th box asks them to enter cost
per widget. The 6th box multiples these figures and calculates total cost in
local currency.

For example, a person might type in Widgets, total amount 5, they might
choose US$ from the drop down box, and get an exchange rate of 0.5 as the
result in box 4, at a cost of $1 per widget. The answer comes out in £s at
£2.50.

So far, so good.

The problem starts if they want to add more items. To make this possible, I
wanted to create a macro that would copy the row in which all this data has
been enetered and to duplicate it as a second row. The problem I found was
that the drop down box won't work properly. If you open the format control
for the drop down box in row two, the Cell Link will be the same cell as the
drop down box in row one which means the two rows are now linked. Changing
the cell reference to non-absolute makes no difference.

There are two other problems connected with this, but I need to fix this one
first. If anyone can help or point me to someone who can I would be very
grateful.

Thank you.

Dom