Validation from list in different worksheet/workbook

[Guest]

I am using Excel 97, I am wanting to utilise a list from another
worksheet/workbook for data validation. Is there anyway this can be done
either via the frontend or programmatically?

Thanks in hope

 

[keepITcool]

Define a name for the range via INsert/Names/Define.
assume list is on sheetDB, validation on sheetINP

valid names would be
sheetinp!valList1 : =sheetDB!a1:b20 (local name on sheetInp)
valList2 : =sheetDB!a1:b20 (global name)

those can then be used a source for validation list.
in DV dialog press f3 to popup available names..






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Mark wrote :

 

[Ed Ferrero]

HI Mark,

I think you will have to import the list into your workbook.
Use Data - Get Data...

Ed Ferrero
http://edferrero.m6.net

 

[DM Unseen]

simple use =INDIRECT("Mysheet!MyList") or =INDIRECT(Mysheet!A1:A9") as
formula when selecting list validations. Excel will not be able to spot
you cheating<evil grin>


DM Unseen

 

[Ed Ferrero]

But can you use a list in another workbook?

Ed Ferrero
http://edferrero.m6.net

 

[Tom Ogilvy]

Yes, but the other workbook has to be open as I recollect. The alternative
(to leave the source book closed) would be to duplicate the data in the
current workbook, perhaps on a hidden sheet, using linking formulas in the
cells. then have the defined name refer to the local preproduction and use
the defined name in the data validation.

 

[DM Unseen]

Only when it is open,

INDIRECT can be used with referencing another open workbook, but not a
closed one.

There are alternatives for INDIRECT on a closed workbook, but if they
will work in a validation list I don't know.
(Posted that 2 days ago) see
http://groups-beta.google.com/group...5c6867e10e0/6d21139c2e690340#6d21139c2e690340

DM Unseen