create drop down list from another sheet and with manual data

[deepu.gouwtham]

Hi All,

I want to create a drop down list from another sheet as well to have manual data.

I have created a drop down list from another worksheet by
data validation - > list ->source
='sheet1'!B14:B59994

Now how to include "All" value into the same drop down list.

I dont want to display data "All" in the sheet1 but i want to display into the drop down list.

please help me to resolve the issue

[pike]

Hi deepu.gouwtham
to create a list from another worksheet use a defined Name
to enter any value > validation list > Error Alerts and unckeck alert display

[deepu.gouwtham]

Hi Pike,
Thanks for the reply.
I want to display "All" in the drop down list.
Assume that I'm listing a drop down list from another sheet with below names,
Chris,Tim,Ireland,Bel,Patricia.
Now i want to include "ALL" into the same drop down list,so the user can select "ALL" from the drop down list.
please suggest me how to include "All" value into the drop down list along with displaying above namesfrom another sheet

[pike]

deepu.gouwtham

in List "source"

Please Login or Register  to view this content.

[deepu.gouwtham]

I'am getting an exception as "The formula you typed contains an error"
I have tried all these below approaches in List -> source
='"ALL',LocClassRange
='ALL',LocClassRange
=LocClassRange,"ALL"
Note: LocClassRange - is the named range

[Haseeb Avarakkan]

Hello Deepu,

Just add "ALL" in the first row of the named range.

[deepu.gouwtham]

Dear Haseeb,

"ALL" should not be displayed on the named range.
I want it to display only on the drop down list .

[pike]

Hi deepu.gouwtham,
Only option i can think of is to add worksheet even code
to change the value of the cell to "" it All is selected from list

Please Login or Register  to view this content.