excel pull-down menu question

[TdC]

dudes,

I can make a pull down menu in excel through the data > validation > list thingy, but I want the pull-down arrow to constantly be visible. can anyone help?

TIA

 

[Alan]

Use a combo box

Good example here -> http://www.contextures.com/xlDataVal11.html

A bit overkill but im not aware of any easier way.

 

[TdC]

thanks, but the code won't work for me

 

[Alan]

Forget the code, the idea is to use the design view and manually add a combo box instead of a data-validation list ... i think

ohh and the code thing will almost certainly be due to macro protection - tools - macro - security

 

[Alan]

Going to be alot of work but as a step by step :-

- Start a blank excel book
- Put the following into A1:Red A2:Yellow A3:Green
- View -> Toolbars -> Control Toolbox
- Click on the top left icon to take you into design mode
- Click on a combo box then click somewhere on the sheet
- Right click on your newly placed combo box and select properties
- Change "ListFillRange" to A1:C1
- Change "MatchRequired" to True
- Change "LinkedCell" to A5
- Close properties window
- Click on the design mode icon again to exit design mode

Should give the idea of what i mean

 

[TdC]

yarr! smashing! that's what I require

 

[TdC]

erk! other formulae don't react to the pull-down's choices

 

[JingleBells]

Data -> Filter -> AutoFilter

You may have to use the Advanced Filter, but thats what you want certainly.

 

[Alan]

erk! other formulae don't react to the pull-down's choices

Did you do this part :-
- Change "LinkedCell" to A5

Then the result of what you picked would go into cell A5 and you can base your formula on that.

 

[TdC]

nah JB, iirc you can only use one autofilter at a time (don't call me on this, because excel guru I am not hehe)

Tears: yeah mate, I see what you mean. I could make a "console" thingy in the spreadsheet with the pulldown menus, and then use the menus to generate the text and layout I'm after in a different part of the sheet.

I don't have office at home, or I'd test it. Tomorrow at workies will be the thing then.

Thanks for the efforts guys!

 

[Alan]

nah JB, iirc you can only use one autofilter at a time (don't call me on this, because excel guru I am not hehe)

Tears: yeah mate, I see what you mean. I could make a "console" thingy in the spreadsheet with the pulldown menus, and then use the menus to generate the text and layout I'm after in a different part of the sheet.

I don't have office at home, or I'd test it. Tomorrow at workies will be the thing then.

Thanks for the efforts guys!

There must be an easier way - excel sucks anyway ask me a Citrix question

 

[JingleBells]

TdC, i'm not sure exactly what you want, something like this is what I was imagining:

http://www.pete-b.co.uk/files/XLSExample.zip

 

[TdC]

yeah, the pull-down arrow box is what I want, but I don't want to use autofilter

EXCEL SUCKS!!!1

 

[TdC]

another question:

I'm using a function to find certain text:

=IF((FIND.ALL("FEE";D7;1));"foo";"bar")

if cell D7 is "FEE" then the output of the function is "foo", but if D7 isn't "FEE" but something else the function doesn't return "bar" but "#VALUE!"

what am I doing wrong?

 

[Alan]

another question:

I'm using a function to find certain text:

=IF((FIND.ALL("FEE";D7;1));"foo";"bar")

if cell D7 is "FEE" then the output of the function is "foo", but if D7 isn't "FEE" but something else the function doesn't return "bar" but "#VALUE!"

what am I doing wrong?

use a , not a ; "foo,"bar") maybe

 

[TdC]

never mind, I've sussed that IF thing

 

[Alan]

what am I doing wrong?

Working in IT ?

 

[TdC]

use a , not a ; "foo,"bar") maybe

but the excel formula wizard says I have to use the ;

 

[TdC]

Working in IT ?

hehe I prefer to think "excel is poo"