I need to add a legend to several pages of a workbook. I'd like to have all the legends linked to a common source so it can be edited in one place. I want to be able to reposition the legend on the page to move it out of the way or delete it if the user doesn't want it.
I found that if I Insert an Object and Create From File as a Link and choose an Excel spreadsheet I get something pretty close to what I want. But I don't want my users to have to keep track of a seperate file just for the legend so why not point to a different tab on the current spreadsheet? I just can't seem to work out a syntax to do this.
If I click on my linked object I see the formula: =Excel.Sheet.12|'C:\DATA\USERS\Pixelman\Legend.xlsx'!''''
I can simplify the path to =Excel.Sheet.12|Legend.xlsx!'''' and the link will still work; that is I can double click on the inserted region and edit the sheet.
If I try to specify a sheet like so, =Excel.Sheet.12|[Legend.xlsx]Sheet2!'''' Excel does not complain about syntax but the link no longer works.
I can edit the reference to point to the current workbook and that will work but it will show a default sheet and I need it to show a particular sheet. It can also get balled up in a circular reference and crash in a horrible way but that's to be expected.
I'm using Excel 2007 and my solution needs to be compatible with this version.
I found that if I Insert an Object and Create From File as a Link and choose an Excel spreadsheet I get something pretty close to what I want. But I don't want my users to have to keep track of a seperate file just for the legend so why not point to a different tab on the current spreadsheet? I just can't seem to work out a syntax to do this.
If I click on my linked object I see the formula: =Excel.Sheet.12|'C:\DATA\USERS\Pixelman\Legend.xlsx'!''''
I can simplify the path to =Excel.Sheet.12|Legend.xlsx!'''' and the link will still work; that is I can double click on the inserted region and edit the sheet.
If I try to specify a sheet like so, =Excel.Sheet.12|[Legend.xlsx]Sheet2!'''' Excel does not complain about syntax but the link no longer works.
I can edit the reference to point to the current workbook and that will work but it will show a default sheet and I need it to show a particular sheet. It can also get balled up in a circular reference and crash in a horrible way but that's to be expected.
I'm using Excel 2007 and my solution needs to be compatible with this version.
