Excel Problem : Enter data in a cell from a list

  sav 12:27 23 Aug 04
Locked

Enter data in a cell from a list you specify

I am trying to create a dropdown list of valid entries in a spreadsheet to make data entry easier and more consistant. As the list isn’t continuous I am unable to use the ‘Pick a List’ feature from the right-click menu. I have created a list in another workbook and given it a name, but I am unable to link this list to my original spreadsheet (the options are greyed out). When I cut and paste this list to the spreadsheet in question, I am able to make the link. The problem with this is that I would like several spreadsheets to use the same ‘master’ list so that it is easier to update.

Can anyone help?

Thanks

  VoG II 12:53 23 Aug 04

Right click the drop-down, select Format Control.

In the Input Range box enter the path to your master list, e.g.

'Master'!$A$2:$A$200

  pc moron 13:17 23 Aug 04

When I tried this a while ago Excel returned this:

"You may not use references to other worksheets or workbooks for Data Validation criteria"

I couldn't get Data Validation to work from an external link.

  Simsy 13:21 23 Aug 04

(though this is off the top of my head, and untested), is have the list in a master, and then, in each ssheet have another list that refers to the master.

The data validation comes from the list in each sheet.

I'll just go away and test what I mean!

Regards,

Simsy

  VoG II 13:21 23 Aug 04

I was assuming that this was a simple drop-down list created using the Forms toolbar.

If it is a Data Validation list then, no, the above won't work.

  Simsy 13:37 23 Aug 04

Create a worksheet that you want to have your master data, and save it. Lets call it "testdata.xls" I've entered some figures in the range A1:A20

On your new spreadsheet, on sheet2, in cell A1 enter the following formula;

=[testdata.xls]Sheet1'!A1 (you may need to add the path to the file on the front of this formula...)

Copy this formula down A1:A20

Name this range, "PermittedEntries"

Now for the cells you want to apply it to, select data validation, choose "List" and for "source", put =PermittedEntries

Now the validation will work as you want, but, when you open the file you will be asked if you want to update the links. Also, changing the data in the master will not have an effect unless the spreadsheets have the links updated.

I hope this makes sense!

Regards,

Simsy

  Simsy 13:38 23 Aug 04

Create a worksheet that you want to have your master data, and save it. Lets call it "testdata.xls" I've entered some figures in the range A1:A20

On your new spreadsheet, on sheet2, in cell A1 enter the following formula;

=[testdata.xls]Sheet1'!A1 (you may need to add the path to the file on the front of this formula...)

Copy this formula down A1:A20

Name this range, "PermittedEntries"

Now for the cells you want to apply it to, select data validation, choose "List" and for "source", put =PermittedEntries

Now the validation will work as you want, but, when you open the file you will be asked if you want to update the links. Also, changing the data in the master will not have an effect unless the spreadsheets have the links updated.

I hope this makes sense!

Regards,

Simsy

  sav 13:41 23 Aug 04

Sorry about the confusion, I should have made it clear that I was talking about Data Validation.

I have just discovered that if I have the source spreadsheet (master) open the drop down works, as soon as it's closed then it stops working.

Simsy, I will give your suggestion a go.

Thankls all.

  pc moron 14:14 23 Aug 04

Nice one Simsy.

Sav, if the source workbook is closed you have to enter the complete path to the source data.

See Simsy's post about adding the path to the formula.

  sav 14:36 23 Aug 04

Thanks Simsy, it works a treat!

Thanks all for your response.

Sav

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Here's what should be coming to Adobe Project Felix in 2017

Apple AirPods review: Apple's beautiful new Bluetooth headphones bring true intelligence to…