I have a vlookup formula doing a different calculation depending of the value
in a drop down list. The format of the result of the calculation shall be
either a number or a percentage depending on the value of the same drop down
list. I see two options, either a macro that runs automatically when I change
the drop down list and that updates the format of my cells or a formula that
converts the format from number to percentage.
How do I run a macro based on a change in a drop down list?
Is there any formula to convert the format from number to percentage?

Thanks a lot!

Define Drop down list?
Combo Box from Control Toolbox? Combo Box from Forms Toolbar? Data
Validation List?
--
HTH...

Jim Thomlinson


"Bonobo" wrote:

I have a vlookup formula doing a different calculation depending of the value
in a drop down list. The format of the result of the calculation shall be
either a number or a percentage depending on the value of the same drop down
list. I see two options, either a macro that runs automatically when I change
the drop down list and that updates the format of my cells or a formula that
converts the format from number to percentage.
How do I run a macro based on a change in a drop down list?
Is there any formula to convert the format from number to percentage?

Thanks a lot!

There's no formula that will change the numberformat of any cell.

But depending on what you used to create the dropdown, you may be able to use a
linked cell and use that in a formula that will "adjust" the original number.

Say A1 is your linked cell and it can be Pct (or something else).
And A2 is your cell with the number.
You could put this in A3:

=if(a1="pct",a2/100,a2)
or
=a2/(if(a1="pct",100,1)

You may be able to embed that same kind of formula into any existing formula--or
just adjust any existing formula to point at that 3rd cell.



Bonobo wrote:

I have a vlookup formula doing a different calculation depending of the value
in a drop down list. The format of the result of the calculation shall be
either a number or a percentage depending on the value of the same drop down
list. I see two options, either a macro that runs automatically when I change
the drop down list and that updates the format of my cells or a formula that
converts the format from number to percentage.
How do I run a macro based on a change in a drop down list?
Is there any formula to convert the format from number to percentage?

Thanks a lot!

--

Dave Peterson

Thank you Dave, but this will not show the result as percentage... ie with
the % sign. what about running a macro whenever I change the selection in the
drop down list, any idea?

"Dave Peterson" wrote:

There's no formula that will change the numberformat of any cell.

But depending on what you used to create the dropdown, you may be able to use a
linked cell and use that in a formula that will "adjust" the original number.

Say A1 is your linked cell and it can be Pct (or something else).
And A2 is your cell with the number.
You could put this in A3:

=if(a1="pct",a2/100,a2)
or
=a2/(if(a1="pct",100,1)

You may be able to embed that same kind of formula into any existing formula--or
just adjust any existing formula to point at that 3rd cell.



Bonobo wrote:

I have a vlookup formula doing a different calculation depending of the value
in a drop down list. The format of the result of the calculation shall be
either a number or a percentage depending on the value of the same drop down
list. I see two options, either a macro that runs automatically when I change
the drop down list and that updates the format of my cells or a formula that
converts the format from number to percentage.
How do I run a macro based on a change in a drop down list?
Is there any formula to convert the format from number to percentage?

Thanks a lot!

--

Dave Peterson

The code would depend on the type of dropdown you used.

Did you use a combobox from the control toolbox toolbar?
Did you use a dropdown from the Forms toolbar?
Did you use data|validation (and what version of excel do you use?)

Bonobo wrote:

Thank you Dave, but this will not show the result as percentage... ie with
the % sign. what about running a macro whenever I change the selection in the
drop down list, any idea?

"Dave Peterson" wrote:

There's no formula that will change the numberformat of any cell.

But depending on what you used to create the dropdown, you may be able to use a
linked cell and use that in a formula that will "adjust" the original number.

Say A1 is your linked cell and it can be Pct (or something else).
And A2 is your cell with the number.
You could put this in A3:

=if(a1="pct",a2/100,a2)
or
=a2/(if(a1="pct",100,1)

You may be able to embed that same kind of formula into any existing formula--or
just adjust any existing formula to point at that 3rd cell.



Bonobo wrote:

I have a vlookup formula doing a different calculation depending of the value
in a drop down list. The format of the result of the calculation shall be
either a number or a percentage depending on the value of the same drop down
list. I see two options, either a macro that runs automatically when I change
the drop down list and that updates the format of my cells or a formula that
converts the format from number to percentage.
How do I run a macro based on a change in a drop down list?
Is there any formula to convert the format from number to percentage?

Thanks a lot!

--

Dave Peterson


--

Dave Peterson

I am using data validation in Excel 2003

"Dave Peterson" wrote:

The code would depend on the type of dropdown you used.

Did you use a combobox from the control toolbox toolbar?
Did you use a dropdown from the Forms toolbar?
Did you use data|validation (and what version of excel do you use?)

Bonobo wrote:

Thank you Dave, but this will not show the result as percentage... ie with
the % sign. what about running a macro whenever I change the selection in the
drop down list, any idea?

"Dave Peterson" wrote:

There's no formula that will change the numberformat of any cell.

But depending on what you used to create the dropdown, you may be able to use a
linked cell and use that in a formula that will "adjust" the original number.

Say A1 is your linked cell and it can be Pct (or something else).
And A2 is your cell with the number.
You could put this in A3:

=if(a1="pct",a2/100,a2)
or
=a2/(if(a1="pct",100,1)

You may be able to embed that same kind of formula into any existing formula--or
just adjust any existing formula to point at that 3rd cell.



Bonobo wrote:

I have a vlookup formula doing a different calculation depending of the value
in a drop down list. The format of the result of the calculation shall be
either a number or a percentage depending on the value of the same drop down
list. I see two options, either a macro that runs automatically when I change
the drop down list and that updates the format of my cells or a formula that
converts the format from number to percentage.
How do I run a macro based on a change in a drop down list?
Is there any formula to convert the format from number to percentage?

Thanks a lot!

--

Dave Peterson


--

Dave Peterson

I managed to get what I want with the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Range("A4") = "%" Then
Range("C4:D5").Select
Selection.Style = "Percent"
End If
If Range("A4") = "#" Then
Range("C4:D5").Select
Selection.NumberFormat = "#,##0.00"
End If
End If
End Sub

Thanks anyway!

"Dave Peterson" wrote:

The code would depend on the type of dropdown you used.

Did you use a combobox from the control toolbox toolbar?
Did you use a dropdown from the Forms toolbar?
Did you use data|validation (and what version of excel do you use?)

Bonobo wrote:

Thank you Dave, but this will not show the result as percentage... ie with
the % sign. what about running a macro whenever I change the selection in the
drop down list, any idea?

"Dave Peterson" wrote:

There's no formula that will change the numberformat of any cell.

But depending on what you used to create the dropdown, you may be able to use a
linked cell and use that in a formula that will "adjust" the original number.

Say A1 is your linked cell and it can be Pct (or something else).
And A2 is your cell with the number.
You could put this in A3:

=if(a1="pct",a2/100,a2)
or
=a2/(if(a1="pct",100,1)

You may be able to embed that same kind of formula into any existing formula--or
just adjust any existing formula to point at that 3rd cell.



Bonobo wrote:

I have a vlookup formula doing a different calculation depending of the value
in a drop down list. The format of the result of the calculation shall be
either a number or a percentage depending on the value of the same drop down
list. I see two options, either a macro that runs automatically when I change
the drop down list and that updates the format of my cells or a formula that
converts the format from number to percentage.
How do I run a macro based on a change in a drop down list?
Is there any formula to convert the format from number to percentage?

Thanks a lot!

--

Dave Peterson


--

Dave Peterson

If you want to try, rightclick on the worksheet tab that has the
data|validation. Select view code and paste this in the codewindow:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myOtherCell As Range
Set myOtherCell = Me.Range("c3")

With Target
If .Cells.Count 1 Then Exit Sub
If Intersect(.Cells, Me.Range("a1")) Is Nothing Then Exit Sub

If LCase(.Value) = "pct" Then
myOtherCell.NumberFormat = "0.00%"
Else
myOtherCell.NumberFormat = "0.00"
End If
End With

End Sub

I used A1 as the cell with data|validation and changed the format for cell C3.
Change to what you need.



Bonobo wrote:

I am using data validation in Excel 2003

"Dave Peterson" wrote:

The code would depend on the type of dropdown you used.

Did you use a combobox from the control toolbox toolbar?
Did you use a dropdown from the Forms toolbar?
Did you use data|validation (and what version of excel do you use?)

Bonobo wrote:

Thank you Dave, but this will not show the result as percentage... ie with
the % sign. what about running a macro whenever I change the selection in the
drop down list, any idea?

"Dave Peterson" wrote:

There's no formula that will change the numberformat of any cell.

But depending on what you used to create the dropdown, you may be able to use a
linked cell and use that in a formula that will "adjust" the original number.

Say A1 is your linked cell and it can be Pct (or something else).
And A2 is your cell with the number.
You could put this in A3:

=if(a1="pct",a2/100,a2)
or
=a2/(if(a1="pct",100,1)

You may be able to embed that same kind of formula into any existing formula--or
just adjust any existing formula to point at that 3rd cell.



Bonobo wrote:

I have a vlookup formula doing a different calculation depending of the value
in a drop down list. The format of the result of the calculation shall be
either a number or a percentage depending on the value of the same drop down
list. I see two options, either a macro that runs automatically when I change
the drop down list and that updates the format of my cells or a formula that
converts the format from number to percentage.
How do I run a macro based on a change in a drop down list?
Is there any formula to convert the format from number to percentage?

Thanks a lot!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson