Do I need VBA for this?

[Ramy27]

In Excel (XP), I'm trying to create a drop down menu for some cells where the menu should be filled by a list of data from a different spreadsheet. Obviously I can't use data validation, because it doesn't won't allow list from other spreadsheets. Do I need VBA for this? The second spread sheet keeps changing, so I need to keep updating the list everytime the user clicks on that cells or something.

---------------------------------
"Good things are the enemies of the best things"
---------------------------------

 

[SkipVought]

Ramanesh,

If you name the range on the other sheet, you can use Data/Validation using the range name.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Ramy27]

is it
'sheetname'!rangename


---------------------------------
"Good things are the enemies of the best things"
---------------------------------

 

[Ramy27]

Hi SkipVought,
Thanks, that helps. But I have a problem here though...
1) It doesn't provide a drop down menu
2) As well as the list given in the NAMED RANGE, I want the user also to be able to enter his own data, if it is not listed in the named range.

R.


---------------------------------
"Good things are the enemies of the best things"
---------------------------------

 

[Loomah]

You could remove the error alert option?

Happy Friday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Ramy27]

Loomah,
it answers my Question 2, thanks. why can't I see the drop down list.

1) I tried "custom" and then range name. It didn't give a drop down

2) then I tried "list" and then range name. This gave me a drop down menu, but the list had only one word..the name of that range

---------------------------------
"Good things are the enemies of the best things"
---------------------------------

 

[byundt]

You should be using "List", but you probably forgot the = in front of your range name in the "Source" field of the Data...Validation menu item
=MyRangeName

 

[SkipVought]

use a combobox since you're adding names. then run this code when a new name is entered...

With [MyRange]
  .Parent.Cells(.Row + .Rows.Count, .Column).Value = combobox1.Value
  application.displayalerts=false
  .CurrentRegion.CreateNames Top:=True
  application.displayalerts=true
End With

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}