cell validation list reference to a different worksheet

[Mark J Kubicki]

i have a validation list created thru the ususal XL/data/validation...
it refences data in a different worksheet using a named range (created thru
XL/insert/name/define...)
the named range is defined by this formula:
=OFFSET('\\Server\Files\@ Timesheets\[Job Nos for Timesheets.xls]Project
List'!$F$3,0,0,COUNTA ('\\Server\Files\@ Timesheets\[Job Nos for
Timesheets.xls]Project List'!$F$2:$F$506),1)

as the worksheet evolves, it would make sence for the list to vary...
so, i tried redefining it thru VB, its result in an error...


actually, if i do nothing but create a named reference to a list in a
different workbook
ex: '='[Job Nos for Timesheets.xls]Project List'!$F2:$F500"
--->>> i get an error: "cannot reference a different workbook for
validation... or something like that... or must be a delimited list..." UGH!
yet i had been previously ???

as always, thanks in advance, mark

 

[Rob van Gelder]

Why not create a new list in your workbook which is a bunch of formulas
referencing "Job Nos for Timesheets.xls"

 

[Mark J Kubicki]

huh?

Why not create a new list in your workbook which is a bunch of formulas
referencing "Job Nos for Timesheets.xls"


--
Rob van Gelder - http://www.vangelder.co.nz/excel

i have a validation list created thru the ususal XL/data/validation...
it refences data in a different worksheet using a named range (created
thru XL/insert/name/define...)
the named range is defined by this formula:
=OFFSET('\\Server\Files\@ Timesheets\[Job Nos for
Timesheets.xls]Project List'!$F$3,0,0,COUNTA ('\\Server\Files\@
Timesheets\[Job Nos for Timesheets.xls]Project List'!$F$2:$F$506),1)

as the worksheet evolves, it would make sence for the list to vary...
so, i tried redefining it thru VB, its result in an error...


actually, if i do nothing but create a named reference to a list in a
different workbook
ex: '='[Job Nos for Timesheets.xls]Project List'!$F2:$F500"
--->>> i get an error: "cannot reference a different workbook for
validation... or something like that... or must be a delimited list..."
UGH! yet i had been previously ???

as always, thanks in advance, mark

 

[Rob van Gelder]

You've got a named range pointing to another workbook.
Why not make the named range point to a new list in the current workbook.
The new list points to the other workbook.

--
Rob van Gelder - http://www.vangelder.co.nz/excel

huh?

Why not create a new list in your workbook which is a bunch of formulas
referencing "Job Nos for Timesheets.xls"


--
Rob van Gelder - http://www.vangelder.co.nz/excel

i have a validation list created thru the ususal XL/data/validation...
it refences data in a different worksheet using a named range (created
thru XL/insert/name/define...)
the named range is defined by this formula:
=OFFSET('\\Server\Files\@ Timesheets\[Job Nos for
Timesheets.xls]Project List'!$F$3,0,0,COUNTA ('\\Server\Files\@
Timesheets\[Job Nos for Timesheets.xls]Project List'!$F$2:$F$506),1)

as the worksheet evolves, it would make sence for the list to vary...
so, i tried redefining it thru VB, its result in an error...


actually, if i do nothing but create a named reference to a list in a
different workbook
ex: '='[Job Nos for Timesheets.xls]Project List'!$F2:$F500"
--->>> i get an error: "cannot reference a different workbook for
validation... or something like that... or must be a delimited list..."
UGH! yet i had been previously ???

as always, thanks in advance, mark

 

[Mark J Kubicki]

so, on a (hidden) worksheet, I would have a range of cells each with values:
='OtherWorkbook'!A1:A...
and then I would create a named range in the current workbook pointing to
the range of cells referencing the other workbook?
(what happens if the cells in the other workbook are moved, or... then the
references would be incorrect, and correcting the misdirect would be
cumbersome at best -no?)

not to sound un-appreciating; but, this seems a bit like a Band-Aid ?!?!
I don't understand why the range reference has worked for me in one
instance, and not in the other?

however, if this is the best way to get to the goal (?)

---mark

You've got a named range pointing to another workbook.
Why not make the named range point to a new list in the current workbook.
The new list points to the other workbook.

--
Rob van Gelder - http://www.vangelder.co.nz/excel

huh?

Why not create a new list in your workbook which is a bunch of formulas
referencing "Job Nos for Timesheets.xls"


--
Rob van Gelder - http://www.vangelder.co.nz/excel


i have a validation list created thru the ususal XL/data/validation...
it refences data in a different worksheet using a named range (created
thru XL/insert/name/define...)
the named range is defined by this formula:
=OFFSET('\\Server\Files\@ Timesheets\[Job Nos for
Timesheets.xls]Project List'!$F$3,0,0,COUNTA ('\\Server\Files\@
Timesheets\[Job Nos for Timesheets.xls]Project List'!$F$2:$F$506),1)

as the worksheet evolves, it would make sence for the list to vary...
so, i tried redefining it thru VB, its result in an error...


actually, if i do nothing but create a named reference to a list in a
different workbook
ex: '='[Job Nos for Timesheets.xls]Project List'!$F2:$F500"
--->>> i get an error: "cannot reference a different workbook for
validation... or something like that... or must be a delimited list..."
UGH! yet i had been previously ???

as always, thanks in advance, mark

 

[Rob van Gelder]

You're probably right... I've dug a little deeper.
Another way is to use two Named Ranges, one for the source, one for the
destination.

Say you have two workbooks, Master.xls, Slave.xls
You want to create a dropdown box in Slave.xls using a list from Master.xls

Master.xls has a list of values A1:A30
Create a named range in Master.xls called MasterList refers to A1:A30
Create a named range in Slave.xls called SlaveList refers to
=Master.xls!MasterList
Data Validation List Source =SlaveList

Dropdown only works when Master.xls is open.

This works for me XL2003

--
Rob van Gelder - http://www.vangelder.co.nz/excel

so, on a (hidden) worksheet, I would have a range of cells each with
values: ='OtherWorkbook'!A1:A...
and then I would create a named range in the current workbook pointing to
the range of cells referencing the other workbook?
(what happens if the cells in the other workbook are moved, or... then
the references would be incorrect, and correcting the misdirect would be
cumbersome at best -no?)

not to sound un-appreciating; but, this seems a bit like a Band-Aid ?!?!
I don't understand why the range reference has worked for me in one
instance, and not in the other?

however, if this is the best way to get to the goal (?)

---mark

You've got a named range pointing to another workbook.
Why not make the named range point to a new list in the current workbook.
The new list points to the other workbook.

--
Rob van Gelder - http://www.vangelder.co.nz/excel

huh?

message Why not create a new list in your workbook which is a bunch of formulas
referencing "Job Nos for Timesheets.xls"


--
Rob van Gelder - http://www.vangelder.co.nz/excel


i have a validation list created thru the ususal XL/data/validation...
it refences data in a different worksheet using a named range (created
thru XL/insert/name/define...)
the named range is defined by this formula:
=OFFSET('\\Server\Files\@ Timesheets\[Job Nos for
Timesheets.xls]Project List'!$F$3,0,0,COUNTA ('\\Server\Files\@
Timesheets\[Job Nos for Timesheets.xls]Project List'!$F$2:$F$506),1)

as the worksheet evolves, it would make sence for the list to vary...
so, i tried redefining it thru VB, its result in an error...


actually, if i do nothing but create a named reference to a list in a
different workbook
ex: '='[Job Nos for Timesheets.xls]Project List'!$F2:$F500"
--->>> i get an error: "cannot reference a different workbook for
validation... or something like that... or must be a delimited
list..." UGH! yet i had been previously ???

as always, thanks in advance, mark

 

[Mark J Kubicki]

thanks...
that makes more sense, AND, it worked too !!!

mark
-----------------------------------------------------

You're probably right... I've dug a little deeper.
Another way is to use two Named Ranges, one for the source, one for the
destination.

Say you have two workbooks, Master.xls, Slave.xls
You want to create a dropdown box in Slave.xls using a list from
Master.xls

Master.xls has a list of values A1:A30
Create a named range in Master.xls called MasterList refers to A1:A30
Create a named range in Slave.xls called SlaveList refers to
=Master.xls!MasterList
Data Validation List Source =SlaveList

Dropdown only works when Master.xls is open.

This works for me XL2003

--
Rob van Gelder - http://www.vangelder.co.nz/excel

so, on a (hidden) worksheet, I would have a range of cells each with
values: ='OtherWorkbook'!A1:A...
and then I would create a named range in the current workbook pointing to
the range of cells referencing the other workbook?
(what happens if the cells in the other workbook are moved, or... then
the references would be incorrect, and correcting the misdirect would be
cumbersome at best -no?)

not to sound un-appreciating; but, this seems a bit like a Band-Aid ?!?!
I don't understand why the range reference has worked for me in one
instance, and not in the other?

however, if this is the best way to get to the goal (?)

---mark

You've got a named range pointing to another workbook.
Why not make the named range point to a new list in the current
workbook.
The new list points to the other workbook.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


huh?

message Why not create a new list in your workbook which is a bunch of
formulas referencing "Job Nos for Timesheets.xls"


--
Rob van Gelder - http://www.vangelder.co.nz/excel


i have a validation list created thru the ususal XL/data/validation...
it refences data in a different worksheet using a named range
(created thru XL/insert/name/define...)
the named range is defined by this formula:
=OFFSET('\\Server\Files\@ Timesheets\[Job Nos for
Timesheets.xls]Project List'!$F$3,0,0,COUNTA ('\\Server\Files\@
Timesheets\[Job Nos for Timesheets.xls]Project List'!$F$2:$F$506),1)

as the worksheet evolves, it would make sence for the list to vary...
so, i tried redefining it thru VB, its result in an error...


actually, if i do nothing but create a named reference to a list in a
different workbook
ex: '='[Job Nos for Timesheets.xls]Project List'!$F2:$F500"
--->>> i get an error: "cannot reference a different workbook for
validation... or something like that... or must be a delimited
list..." UGH! yet i had been previously ???

as always, thanks in advance, mark