Combo box - input Range

[Guest]

I have a combobox on a work sheet.
I want to set the input range depending upon an if statement...

Input range =if(Width=0.7,Range1,Range2)

typing this statement into the input range does not work.

How can I achieve this?

regards

Wayne

 

[Guest]

What are you really trying to achieve? If width of what = .7?

If width of the Combobox Selection? Cause I don't think it works like that.
Let me know what exactly you are looking to do with this.

 

[Guest]

Basic description....

if the width is 0.7 metres then select a height from the set of possible
values in range1, else if the width isn't 0.7 metres then choose the height
from the set of possible values from range2

 

[Guest]

I don't see the logic behind that. What is the selection that determines if
the width is .7 metres? Because you're formula isn't directed at anything.
It's saying

= IF(the width of NOTHING = .7, Range1, Range2)

It has to be referenced to something.

 

[Dave Peterson]

Do you have to use a combobox?

How about a cell with Data|Validation?

Then you could use List with a source:
=INDIRECT(IF(width=0.7,"range1","range2"))
(notice that the range1 and range2 names are surrounded by quotation marks.)

 

[Dave Peterson]

They looked like range names to me. But I've been wrong before.

 

[Guest]

haha, most likely I'm wrong... I'm still just a beginner. My thought process
was that I knew Range1 and Range2 were probably named ranges, but what is
Width=.7 referring to is my question?

I would think it would have to be more like IF the width of what = .7. I'm
not that familiar with combobox's being able to change ranges though. I
usually use the cell link and just offset based off that. Let me know what
you're thinking and if Im wrong on any of this.

 

[Guest]

Dave

It needs to be a combo box just by the fact that a conbo box has a pull down
icon and the data validation does not untill you click in it.

I know it's no too much of an ask to have people select the cell then the
value, but you'd be supprised on the number of phone calls....

 

[Dave Peterson]

What I'm thinking or what the original poster is thinking?????

I'm thinking that width is a name of a cell and the original poster can't change
the input range this way.

I'm thinking that depending on how that width cell changes, an event macro may
be used to change that input range.

 

[Dave Peterson]

I give the cells that are used for input a fill color of light yellow--to stand
out.

If that's not enough, maybe you could add a shape to the cell that looks like a
dropdown.

Alternatively, maybe you could use an event macro that changes that input range.

For instance, if you're changing Width by typing, you could tie into the
worksheet_Change event to change the input range for that dropdown (from the
Forms Toolbar, right????):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myDD As DropDown

If Intersect(Target, Me.Range("width")) Is Nothing Then
Exit Sub
End If

Set myDD = Me.DropDowns("Drop Down 1")

With myDD
'clear the current choice
'since the list is changing
.ListIndex = 0
If Me.Range("Width").Value = 0.7 Then
.ListFillRange = Me.Range("Range1").Address(external:=True)
Else
.ListFillRange = Me.Range("Range2").Address(external:=True)
End If
End With
End Sub

This assumes that all the ranges (width, range1, and range2) are all on the same
sheet.

Some references:

David McRitchie's intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

 

[Dave Peterson]

Just a correction...

This formula:
=INDIRECT(IF(width=0.7,"range1","range2"))
can be:
=IF(width=0.7,range1,range2)

No need for that extra junk.

 

[Guest]

Hi Gang!

The width is actually the shortest base measurement of our scaffolding
product.
Depeding upon the width, determines the maximum height of the scaffold. A
scaffold that is to high for the width runs the risk of toppeling over.

I have several combo boxes that lets the user choose, the width, length and
height of the scaffold.

Therefore if the user selects a base width of 0.7m (2'4"?) there is a set
range of selectable heights. choosing a different width value again results
in an other set of height values etc.

At present I have a data validation set up where the range attached to it
has all the various height options. Using an if satement I can show only the
various heights available for the chosen width (=if(Width=0.7,MyValue,"").

It's cluncky but it works. The real problem is that some users do not know
to click into the cell to bring up the pull down values as the pull down icon
is not shown untill you actually click the cell.

Hope this explains in more detail.

Thanks

Wayne

 

[Dave Peterson]

Did you read that other reply that used a worksheet_change event to change the
input range?

Hi Gang!

The width is actually the shortest base measurement of our scaffolding
product.
Depeding upon the width, determines the maximum height of the scaffold. A
scaffold that is to high for the width runs the risk of toppeling over.

I have several combo boxes that lets the user choose, the width, length and
height of the scaffold.

Therefore if the user selects a base width of 0.7m (2'4"?) there is a set
range of selectable heights. choosing a different width value again results
in an other set of height values etc.

At present I have a data validation set up where the range attached to it
has all the various height options. Using an if satement I can show only the
various heights available for the chosen width (=if(Width=0.7,MyValue,"").

It's cluncky but it works. The real problem is that some users do not know
to click into the cell to bring up the pull down values as the pull down icon
is not shown untill you actually click the cell.

Hope this explains in more detail.

Thanks

Wayne