How do I share a list within a book with multiple worksheets?

[Guest]

I have a book with multiple worksheets where there is a list of items.
Whenever, I create a new worksheet within the book, the list from the other
worksheets is not available. I have to write all the items again. As this is
not a list that I can just copy & paste, I would like to know if there is a
way to share the list within the book.

Thank you

 

[Otto Moehrbach]

Joane
Apparently, what you say is not what you mean. One workbook is one
file. One workbook can have many sheets. Anything that is on any sheet is
available to any other sheet. Exactly what do you mean when you say that a
list is not available to the new sheet? Exactly what do you want to do when
you say you want to "share" the list within the one workbook? Post back and
explain what you want to do, what you are doing, and what happens when you
do it. HTH Otto

 

[Guest]

What I was saying is that within my workbook with multiple worksheets, the
list I have is not shared. When I click "Pick from list" it does not give me
the list that I had on other worksheets within the same workbook.
Is it possible to have the list shared from one sheet to another?

 

[Otto Moehrbach]

Joane
When you say "When I click "Pick from list"", exactly what are you
doing? I mean what/where are you clicking? Are you clicking a Data
Validation cell? A UserForm? Be very specific. Tell us exactly what you
are doing when it works the way you want it to and exactly what you are
doing when it doesn't work. Tell us what sheet holds the list. Tell us
what you are doing in the new or "other" sheet and exactly what happens when
you do it. Do you get an error message? Do you get nothing at all?
And yes, any list on any sheet can be accessed from any other sheet by
certain specific means.
When I say to be specific, I mean like:
I have a list of items in range XX:XX in sheet so-and-so.
I can do such-and-such in that same sheet and I get this-and-that.
When I go to sheet TheName, and do this (step by step) this happens.
HTH Otto

 

[Peo Sjoblom]

Otto,

the list function was added in either 2002 or 2003 and it was discontinued
in 2007.
It's a special function for lists, I guess MS discontinued it because people
were not using it
neither did I so I can't really help somebody using it although it is my
understanding that the lists
are sheet specific so you can't use the same list in other sheets. I have
just out of curiosity created a few lists and what happens is that you will
get a blue border around the list and autofilter applied at the top. It was
replaced by the new table function in Excel 2007 which is pretty nice.
To create a list do data>list>create list


--


Regards,


Peo Sjoblom

 

[Gord Dibben]

Right-click and "Pick from list" will show only unique items from the active
cell column.

You would probably be better of using a Data Validation dropdown menu to select
from.

See Debra Dalgleish's site for more on this. Take special note of how to define
a range on one sheet that can be used as the source for all other sheets.

http://www.contextures.on.ca/xlDataVal01.html


Gord Dibben MS Excel MVP

 

[Otto Moehrbach]

Thanks Peo. I didn't know that. There is no end to this learning business.
Otto