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

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

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

Steve

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Steve –

Great info – thanks!

Bill

Reply | Quote

OK Bill

As you said, <<>> and <<>>

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

Reply | Quote

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

Reply | Quote

This explains it pretty well with data validation.
http://www.contextures.com/xlDataVal02.html%5B/url%5D

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

Steve

Reply | Quote

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!)

Reply | Quote

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

Reply | Quote