ComboBox - Listfillrange - dynamic named ranges

[osx99]

I need to use a combo box to whose selections change based on te results of another cell.

The following formula works fine with data validation on named ranges but I can't get it to work in the combobox listfillrange

=INDIRECT(SUBSTITUTE(b19," ",""))

When I enter the above formula it just resets the listfillrange value to blank

I need to keep the combo box set up. Is this possible?

 

[GlennUK]

Put that formula into a name, and refer to the name in the listfillrange value.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

For Named Ranges, your substitution is incorrect. Substitute an UNDERSCORE for SPACE.
[tt]
=INDIRECT(SUBSTITUTE(b19," ","_"))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[osx99]

Skip - I wasn't substituting for a space I was removing the space to concatenate the two words. Eg Retaining Wall becomes RetainingWall (which will be my named range)

I've tried the above an am getting an error #VALUE! or #REF!

I have a combo box which which is linked to cell B19
Eg when I select Retaining Wall in the combo box - Cell B19 becomes 'Retaining Wall'

In cell AH19 I have =SUBSTITUTE(B19," ","") which returns 'RetainingWall'

However =INDIRECT(SUBSTITUTE(B19," ","")) returns #VALUE!

 

[GlennUK]

However =INDIRECT(SUBSTITUTE(B19," ","")) returns #VALUE!

where arae you using this formula? In other words, where are you seeing #VALUE! ?

Did you try my suggestion at all? ... "Put that formula into a name, and refer to the name in the listfillrange value. "

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[osx99]

I've just tested the above without using combo boxes and it works ok using =INDIRECT(SUBSTITUTE(B19," ","")) in the validation

However, I really do need to use combo boxes and am a bit baffled by this

 

[osx99]

GlennUK - yes tried your method


I've put the formula =INDIRECT(SUBSTITUTE(B19," ","")) in cell AH19 which returns #VALUE!

then named AH19 'test1'

then set the combobox listfill range to 'test1' the combo box then allows me to select #VALUE!

 

[GlennUK]

That's not my method.

My method is doing Insert/Name/Define, choosing a name, say test1, and putting the formula in the "Refers To" box.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[osx99]

Ok Now i see...

I have done insert/name/define chosen name test2 and enter formula - all fine!

Now when I enter test2 in the listfillrange of the combo box it just blanks out? (much like entering the formula itself)

 

[GlennUK]

What is the formula that you've used exactly? The examples I've seen are using relative references, which are dependent on which cell is the active cell.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[osx99]

I've entered the following formula in the 'refer to' box from insert/name/define and chosen name test2

=INDIRECT(SUBSTITUTE(B19," ",""))

B19 contains the result of a combobox linked to this cell of asset types (eg Retaining Walls)

I then have a named range called RetainingWall containing a list of work types (brick,concrete etc)

At the moment I want the formula to remove the space from Retaining Wall and dynamically look up the range named RetainingWall in the combo box

The formula above won't allow me to enter 'test2' into the listfillrange of the combo box

I've also tried =INDIRECT(SUBSTITUTE("B19"," ","")) which does allow me to enter 'test2' but the combo box then has the value of 'RetainingWall' in the list rather than the range RetainingWall

 

[GlennUK]

Change B19 to $B$19 and see if it works.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[osx99]

Ok have replaced refer to with $B$19

slight improvement - now the combo box lists the first item in each range only?

Bizarre? Any ideas to get around this one?

 

[GlennUK]

Please describe what you have done, and where. Are you putting the indirect formula into a defined name? What is the name? What is the formula? What have you used for the ListFillRange entry? What are the definitions of any names referred to in your formulae?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[osx99]

GlennUK - seems to work ok now after i saved and re-opened

Strange! Thanks for all your help

 

[GlennUK]

Phew, thank goodness it's working. Glad to have been of some help.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[osx99]

I'm still having a problem with this. When I close down the excel spreadsheet and re-open the dynamic list in the combo box no longer works. I have to open design mode and retype the range name into the listfillrange for it to work again

Is there a way around this?

 

[osx99]

Seem to have solved it by putting the following in an event

Asset_Material.ListFillRange = "test2

 

[GlennUK]

Are you sure that you've got calculation mode as Automatic?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[osx99]

Yes, was the first thing I checked