| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
cell validation list reference to a different worksheet
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
cell validation list reference to a different worksheet
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 "Mark J Kubicki" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
cell validation list reference to a different worksheet
huh?
"Rob van Gelder" wrote in 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 "Mark J Kubicki" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
cell validation list reference to a different worksheet
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 "Mark J Kubicki" wrote in message ... huh? "Rob van Gelder" wrote in 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 "Mark J Kubicki" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
cell validation list reference to a different worksheet
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 "Rob van Gelder" wrote in message ... 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 "Mark J Kubicki" wrote in message ... huh? "Rob van Gelder" wrote in 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 "Mark J Kubicki" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
cell validation list reference to a different worksheet
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 "Mark J Kubicki" wrote in message ... 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 "Rob van Gelder" wrote in message ... 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 "Mark J Kubicki" wrote in message ... huh? "Rob van Gelder" wrote in 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 "Mark J Kubicki" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
cell validation list reference to a different worksheet
thanks...
that makes more sense, AND, it worked too !!! mark ----------------------------------------------------- "Rob van Gelder" wrote in message ... 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 "Mark J Kubicki" wrote in message ... 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 "Rob van Gelder" wrote in message ... 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 "Mark J Kubicki" wrote in message ... huh? "Rob van Gelder" wrote in 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 "Mark J Kubicki" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Worksheet reference in validation | Excel Discussion (Misc queries) | |||
| validation list on different worksheet | New Users to Excel | |||
| drop-down list validation won't allow a different worksheet | Excel Worksheet Functions | |||
| Data validation list from another worksheet? | Excel Discussion (Misc queries) | |||
| Validation - List - Separate Worksheet | Excel Worksheet Functions | |||
Linear Mode
