Excel - Data Validation.

[AngelofDeath]

I am trying to insert a drop-down list into a cell using Data Validation. The list is from another workbook. I have followed the instructions given in the help file, but evertime I worh through them I end up with the following error message:-

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

This message appears to contradict the information in the help file. Can anyone offer some guidance as to where I am going wrong?

[jordi2k]

The error message pretty much sums up exactly what it says. As far as I'm aware you can't use information from another workbook to populate data validation.

You could script a Macro to pull information across to the workbook and then populate the validation list, however this seems a little counterintuitive since Data Validation tends to be a fixed (non-changing) list of information, therefore pulling this info every time would be a waste of time.

It would be easier to just have the list on a hidden workbook somewhere and reference it that way.

[AngelofDeath]

The help file actually gives instructions to: "Use a different worksheet in a different workbook". That's what is confusing.

I wanted to use a list that is constantly being updated, thereby giving the most current choice each time. I can already achieve this using a different worksheet in the same workbook and it works well. The data I'm looking at is unfortunately in someone elses spreadsheet and it seems a waste of time having to copy this list to my workbook everytime. I shall certainly investigate using a macro.

Thanks.

[jordi2k]

As a general rule of thumb, I'd ignore any and all 'Help' files distributed by Mr Microsoft and his un-helpful team of developers and stick to the forums where people who actually use the software can provide solutions!

In terms of Macro use, I'm not sure how proficient you are with them, but sounds like perhaps the Workbook_Change event or Workbook_Open events may be what your looking for.

If you need help writing something, let me know.

[AngelofDeath]

Hi jordi2k, I've just been seent this link. It may be of interest.

http://www.contextures.com/xlDataVal05.html

[camioneur]

i'm noe expert at this but if you can't create the list because it's in a different workbook, could you create a workshett in your workbook that is linked to the other book with a paste link function from their book to yours .Would that work, do you know what i mean, i'm not very sure i do really!!

[AngelofDeath]

Worth a look camioneur. Thanks.

[AngelofDeath]

camioneur, you can officially upgrade yourself to expert. That works great.

Thanks.

[camioneur]

glad (astonished) to have been of some help!!

Camioneur (expert)

[camioneur]

Just need to brush up on the spelling and (or) tryping errots now