Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Indirect Fn; Combo Boxes and Input Ranges (2000/SR-1)

    Have a spreadsheet with two combo boxes and would like the drop down on the second box (i.e., the "Input range") to change based on the user selection on the first box. I know there is a way to accomplish a similar result using Data|Validation|Settings|(Allow: List) and the Indirect function, but need to use the combo box in this case because the various Input Ranges are on other sheets (which cell validation will not allow). Prefer a non-macro solution - if possible.

    Thanks,
    Bill

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Indirect Fn; Combo Boxes and Input Ranges (2000/SR-1)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Bill

    OK let me get this straight first:

    For example, you have a dropdown with the names of states, and then you have the second one that you want to fill the cities in the state the user picks.

    My suggestion is to have Named Ranges, for the state's names, and then when the user picks a state, you populate the second from the range name picked from the first dropdown's picked item, which is the state's name.

    I hope this is easy to understand.

    Also the bit about the data being in different sheets, well you can always have some linked ranges on the same worksheet and then use this one. Sure you will have some overhead in file size, but you get the job done.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Indirect Fn; Combo Boxes and Input Ranges (2000/SR-1)

    This explains it pretty well with data validation.
    http://www.contextures.com/xlDataVal02.html

    If you use named ranges they can be on another sheet.

    Steve

  4. #4
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indirect Fn; Combo Boxes and Input Ranges (2000/SR-1)

    Not sure I completely understand. On the "Format Control" dialog box for the Combo Box control - there is "Control" tab with an "Input range" field (along with a "Cell link" field and a "Drop down lines" field). It was this "Input range" (for the second Combo Box) where I attempted to use the INDIRECT function - but with no success. You suggest I "populate the second" - but not sure HOW - via a macro?

  5. #5
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indirect Fn; Combo Boxes and Input Ranges (2000/SR-1)

    Would have bet money I had already tried using the named ranges, but anyway it worked...thanks.

    (Still would prefer to use the Combo Box control if possible - since it gives a bit more formatting/placement flexibility - but this will absolutely come in handy!)

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Indirect Fn; Combo Boxes and Input Ranges (2000/SR-1)

    Use RANGE NAMES

    As the input range use a named range like "Combo1Selection"

    Then insert - name - define the "combo1Selection" name to be defined (refers to)
    =INDIRECT(INDEX(cbo1InputRange,cbo1LinkedCell))
    Where:
    cbo1InputRange is input range of combo1 (either range address or named range)
    cbo1LinkedCell is linked cell of combo1(either range address or named range)

    Steve

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Indirect Fn; Combo Boxes and Input Ranges (2000/SR-1)

    The control toolbox items give even more formatting control (you can change the font size, colors and all sort of things in properties. they also can trigger a lot more events to place code in them if desired.

    The forms toolbar items can only trigger 1 event macro.

    Steve

  8. #8
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Indirect Fn; Combo Boxes and Input Ranges (2000/SR-1)

    OK Bill

    As you said, <<< there is ... "Cell link" >>> and <<< there is an "Input range" >>>

    So let the first dropdown fill the Cell Link, with the name of the state, then the second drop down will have its Input Range tied to the Cell Link's value which is the name of the state.

    Is this what you have done? Can you post an example so that we can help you out with something you are more familiar than this State/Cities in a State example?

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  9. #9
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indirect Fn; Combo Boxes and Input Ranges (2000/SR-1)

    Steve -

    I am really trying to understand HOW this is working - I still don't - but it does work. Thanks - I really appreciate your time.

    Bill <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

  10. #10
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indirect Fn; Combo Boxes and Input Ranges (2000/SR-1)

    Wassim -

    Apologize for not being clearer especially on the follow up post. I don't understand Steve's remedy - but it is working - so I will stick with it for now. Thanks for your help and patience.

    My example, by the way, involved more of a hierarchical issue - where the first combo box selects the level within the hierarchy - and the second selects the item to report against (based on the level chosen).

    Thanks again,
    Bill

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Indirect Fn; Combo Boxes and Input Ranges (2000/SR-1)

    What don't you understand and we can try to explain?

    The reference I posted earlier <post#=279192>post 279192</post#> explains the process for data validation and it is very comparable logic to the combobox.

    Steve

  12. #12
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indirect Fn; Combo Boxes and Input Ranges (2000/SR-1)

    Steve -

    I do understand the logic, if you will, of how the Indirect fn works within the data validation - I have used it myself in the past (though not using named ranges from other sheets (Posts 279192 & 279237).

    Fundamentally, I suppose my lack of understanding about the solution involving the Forms Control Combo Box centers around the fact that I was not aware that a range name could be defined as something other than a physical location on a sheet (or sheets).

    Again - thanks so much for the help.

    Bill

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Indirect Fn; Combo Boxes and Input Ranges (2000/SR-1)

    A named range (in reality) is NEVER a location in a sheet. It is a function.

    If you "name" sheet1A1 as "SLA" and you look in the insert name dialog you will see that SLA refers to "=Sheet!$A$1". You could even define it "=6" and use it in calcs without having this value anywhere in the spreadsheet proper. It is NOT the location, but a direct reference to a cell. It can also be a direct reference to another workbook.

    An Aside (a neat item on named FORMULAS:
    Notice in my example that the reference is ABSOLUTE. ($A$1). If you goto B1 and change it so that SLA refered to "=Sheet!A1" (relative) and you put =sla in cell B1 it will give the value of A1. If you put it in C5 it will give you the value of B5! so even though both B1 and C5 have the SAME formula in the cells (=SLA) they yield different RESULTS since the range NAME is relative (you defined it while you were IN B1 to mean =A1 so it is same row 1 col to left. When you use relative reference, it is based on the Active cell (just like conditional formatting)

    Steve

  14. #14
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indirect Fn; Combo Boxes and Input Ranges (2000/SR-1)

    Steve -

    Great info - thanks!

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •