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

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

Thanks for the suggestion. I tried data validation for currency choice and
it worked. I could not get VLOOKUP to work. It does not seem to like it if
the cell in the first part of the formula is a data validation cell - for
example, if the formula is BA3,A2:B43, the data validation cell is BA3.

I also discovered a slight glitch and I was wondering if you know how to fix
it.

A feature of the spreadsheet I am creating is that you can edit the currency
names and exchange rate as you work. I have noticed hwoever, that while
combo boxes automatically update their content if you change the list/s, data
validation cells do not. If you change the cells they reference, they show
#VALUE. To fix this, I have to click on the data vlaidation cell and choose
a new option from its drop down list. It then refreshes and is fine. This
would not be practical for my spreadsheet. Is there a means to get to
one-click-refresh for all?

Thank you.

"JLatham" wrote:

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

Lots of assumptions he that you're using a combo box from the Forms menu,
and that the combo box is aligned pretty well within a cell, and that you're
duplicating rows by some kind of automation?

If this is right then this code, added to after you've duplicated the row
with a new combo box, will set each combo box's .LinkedCell property to the
underlying cell's address

Dim anyShape As Shape
Dim anyCell As Range
...
...your copy/paste/duplicate row code here
...

Set anyCell = ActiveCell
For Each anyShape In ActiveSheet.Shapes
If anyShape.FormControlType = 2 Then ' drop down
anyShape.Select
With Selection
.LinkedCell = anyShape.TopLeftCell.Address
End With
End If
Next
anyCell.Select ' back to where we were


"Dom1966" wrote:

Thanks for the suggestion. I tried data validation for currency choice and
it worked. I could not get VLOOKUP to work. It does not seem to like it if
the cell in the first part of the formula is a data validation cell - for
example, if the formula is BA3,A2:B43, the data validation cell is BA3.

I also discovered a slight glitch and I was wondering if you know how to fix
it.

A feature of the spreadsheet I am creating is that you can edit the currency
names and exchange rate as you work. I have noticed hwoever, that while
combo boxes automatically update their content if you change the list/s, data
validation cells do not. If you change the cells they reference, they show
#VALUE. To fix this, I have to click on the data vlaidation cell and choose
a new option from its drop down list. It then refreshes and is fine. This
would not be practical for my spreadsheet. Is there a means to get to
one-click-refresh for all?

Thank you.

"JLatham" wrote:

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

I probably should add that you could also reset the List range while setting
the .LinkedCell property by adding a line of code like this (change the
actual address of the range to match your setup):

..ListFillRange = "$G$5:$G$7"

This *might* cure your problem with changes in lists resulting in errors
cropping up.

Then, one more assumption, assuming that the currency rate is in the column
next to the list (column H in this case) instead of using a VLOOKUP() to get
the exchange rate, use an =OFFSET() formula, with the reference address 1
cell above the 1st entry in that list, and the row offset portion of it
pointing to the underlying .LinkedCell of the dropdown box. Let's say your
drop down was over C3, then a formula like:
=OFFSET($H$4,C3,0) will return the exchange rate for the currency type chosen.


"Dom1966" wrote:

Thanks for the suggestion. I tried data validation for currency choice and
it worked. I could not get VLOOKUP to work. It does not seem to like it if
the cell in the first part of the formula is a data validation cell - for
example, if the formula is BA3,A2:B43, the data validation cell is BA3.

I also discovered a slight glitch and I was wondering if you know how to fix
it.

A feature of the spreadsheet I am creating is that you can edit the currency
names and exchange rate as you work. I have noticed hwoever, that while
combo boxes automatically update their content if you change the list/s, data
validation cells do not. If you change the cells they reference, they show
#VALUE. To fix this, I have to click on the data vlaidation cell and choose
a new option from its drop down list. It then refreshes and is fine. This
would not be practical for my spreadsheet. Is there a means to get to
one-click-refresh for all?

Thank you.

"JLatham" wrote:

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