external reference error

[shawnvb]

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

 

[Guest]

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.