| 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
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 |
|
#6
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 |
|
#7
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 |
|
#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
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 |
|
#10
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 |
| 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 | |||
Hybrid Mode
