List box linked to many cells automatically

[g3a3n3n3]

Hi

I've got two questions.

First - I've created a list box in excel which, for example, lets say gets it's list values from J1:K3. The list box itself is linked to cell A1 - meaning the chosen value from the list box goes into cell A1. By the way, this list box sits in cell A1. All this works fine. I want to have one list box in every cell from cell A1:A100. How do I do this without manually copying and pasting the list box into every cell and then changing the linked cell the what it needs to be?

Second - How do I make the list box appear only when I select that cell. I.E. When my cursor is in cell A13 - only the list box in cell A13 becomes visible?

All help is much appreciated.

Gillian

 

[KenWright]

I think you may be better off with simple data Validation. Select the entire range, do Data / Validation / Change 'Allow' to 'List' and then use the range selector to select your list of values. Hit Ok and you are done.

Now whenever someone clicks on one of those cells they will see a list of your values to choose from. If you want you can even specify a message to appear when they click on any of the cells.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[g3a3n3n3]

Thanks Ken - thats exactly what I needed to know!!! Everything works really well now! I never knew that function existed!

 

[KenWright]

Glad it worked for you

Also, if you name your DV list (eg MyDVList) and then use the name in your Data validation by putting =MyDVList in the box that comes up with the range selector, then you can easily change one list and all cells linked to it via DV will change automatically to include the new values.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[g3a3n3n3]

Thanks again Ken - will give that a go!!

Cheers
Gillian

 

[g3a3n3n3]

Another query - I have created the drop down list by the using the Tools - Validation and everything works well. The only problem is the font sixe in the list boxes is really small - how do I change this font size?

Thanks
Gillian

 

[KenWright]

:-( That's the one downside, you don't. If you are zoomed to anything less than 100% it becomes very small.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[g3a3n3n3]

well thanks anyway - will try and work with what I've got.

- Gillian