| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combo box - input Range
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo box - input Range
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. "PCH" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo box - input Range
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 "AKphidelt" wrote: 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. "PCH" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo box - input Range
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. "PCH" wrote: 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 "AKphidelt" wrote: 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. "PCH" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo box - input Range
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.) PCH wrote: 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 -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo box - input Range
They looked like range names to me. But I've been wrong before.
AKphidelt wrote: 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. "PCH" wrote: 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 "AKphidelt" wrote: 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. "PCH" wrote: 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 -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo box - input Range
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. "Dave Peterson" wrote: They looked like range names to me. But I've been wrong before. AKphidelt wrote: 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. "PCH" wrote: 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 "AKphidelt" wrote: 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. "PCH" wrote: 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 -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo box - input Range
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" wrote: 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.) PCH wrote: 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 -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo box - input Range
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. AKphidelt wrote: 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. "Dave Peterson" wrote: They looked like range names to me. But I've been wrong before. AKphidelt wrote: 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. "PCH" wrote: 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 "AKphidelt" wrote: 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. "PCH" wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo box - input Range
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 PCH wrote: 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" wrote: 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.) PCH wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo box - input Range
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. Dave Peterson wrote: 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.) PCH wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo box - 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 "Dave Peterson" wrote: 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. AKphidelt wrote: 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. "Dave Peterson" wrote: They looked like range names to me. But I've been wrong before. AKphidelt wrote: 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. "PCH" wrote: 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 "AKphidelt" wrote: 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. "PCH" wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combo box - input Range
Did you read that other reply that used a worksheet_change event to change the
input range? PCH wrote: 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" wrote: 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. AKphidelt wrote: 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. "Dave Peterson" wrote: They looked like range names to me. But I've been wrong before. AKphidelt wrote: 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. "PCH" wrote: 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 "AKphidelt" wrote: 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. "PCH" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Input range | Charts and Charting in Excel | |||
| Combo Box input range automatic update | Excel Discussion (Misc queries) | |||
| combo box range - row | Excel Discussion (Misc queries) | |||
| How can I calculate user input from a combo box into a formula? | Excel Worksheet Functions | |||
| Variable Input Range for Combo Box | Excel Worksheet Functions | |||
Linear Mode
