Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
external reference error
I have a spreadsheet that contains many sheets and there are references to particular ranges all over the place. My aim is to remove a lot of the sheets to an external spreadsheet. I don't want to create named ranges as this would take too much time setting up specific range values. So I want to simply change the reference to an external spreadsheet. An example, I would like to change, form.RowSource = "Functions!a1:a54" to (using H:\[template.xls] as an example) FunctionFormSelector.RowSource = "H:\[template.xls]Functions!a1:a54" (this returns a "Cannot set RowSource property" error, but is just an example as it will be reference elsewhere and not just when setting rowsource) I have searched excel's comprehensive help and haven't been able to find anything. Any clues on correct syntax? Thanks -- shawnvb ------------------------------------------------------------------------ shawnvb's Profile: http://www.excelforum.com/member.php...o&userid=16810 View this thread: http://www.excelforum.com/showthread...hreadid=320066 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
external reference error
I'm not sure this will help but that I have done is to used named ranges
using the offset function. This allows the range to grow/shrink based on values being maintained. I have an external entry workbook and copy the ranges into the main workbook into corresponding spreadsheets so that the entry workbook is available most of the time for entry/maintenance. Here's an example of the named range with offset... =OFFSET('DstTst Data'!$A$16,0,0,COUNTA('DstTst Data'!$A:$A)-12,5) Instead of copying like I do, I think you can specify the workbook like in your example. I believe it becomes a link and when the workbook is open, I think it will ask you if you want to update the links. However, I like the workbooks to be independent until I process the copy function. HTH, Dean. "shawnvb" wrote: I have a spreadsheet that contains many sheets and there are references to particular ranges all over the place. My aim is to remove a lot of the sheets to an external spreadsheet. I don't want to create named ranges as this would take too much time setting up specific range values. So I want to simply change the reference to an external spreadsheet. An example, I would like to change, form.RowSource = "Functions!a1:a54" to (using H:\[template.xls] as an example) FunctionFormSelector.RowSource = "H:\[template.xls]Functions!a1:a54" (this returns a "Cannot set RowSource property" error, but is just an example as it will be reference elsewhere and not just when setting rowsource) I have searched excel's comprehensive help and haven't been able to find anything. Any clues on correct syntax? Thanks -- shawnvb ------------------------------------------------------------------------ shawnvb's Profile: http://www.excelforum.com/member.php...o&userid=16810 View this thread: http://www.excelforum.com/showthread...hreadid=320066 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reference to an external file | Excel Discussion (Misc queries) | |||
External xls reference error | Excel Worksheet Functions | |||
input for external reference | Excel Discussion (Misc queries) | |||
External Reference | Excel Discussion (Misc queries) | |||
external reference | Excel Programming |