Links in same spreadsheet problem

I have a multi-tab spreadsheet with simple links referencing different cells.

The problem I am having is that it the spreadsheet goes through a report generator and it changes the tab names, creating broken links.

Any ideas in solving this problem?

Excel should change the tab names in the linking formulas.

Sheet1!A1 in Sheet2 should change to changed!A1 if Sheet1 gets renamed as
changed.

I have no prblems with Excel 2003 keeping track of sheet name changes.

If you are speaking of Hyperlinks then the name change will screw those up.


Gord Dibben MS Excel MVP


On Fri, 4 Aug 2006 18:11:06 -0400, dyun1dyun1
<dyun1dyun1.2c1dj6_1154729708.875@excelforum-nospam.com> wrote:

>
>I have a multi-tab spreadsheet with simple links referencing different
>cells.
>
>The problem I am having is that it the spreadsheet goes through a
>report generator and it changes the tab names, creating broken links.
>
>Any ideas in solving this problem?

Its the 3rd party report generator that I run my excel sheet through that changes the "names" on the tab that screws up my links.

I do know what naming convention the report writers uses and how it is going to change the name of the tab. I just can't think of a good way to solve my problem. I am leaning towards writing a simple Marco, to edit replace my changed link names to the new names after the report is generated.

But outside of that, I don't have a clue.

dyun1dyun1 ,
In YSL if you want to perfrom cross sheet cell references on a Template/Report sheet, a sheet within the workbook that YSL processes then what you will need to do is the following:
1) First off, do not refernce any cells on a 'Template' sheet in the YSL workbook. A 'Template' sheet is identified as having the name 'Template' or having a name that begins with 'T-' (is 'T-Cash Flow').
2) All references must be made to the 'Report' sheet which is a run-time genrated duplicate of the 'Template' (ie 'T-Cash Flow' iss duplicated and renamed 'Cash Flow').
3) Assume that in a YSL workbook you have the following sheets: T-BalSht, Message, Summary. The T-BalSht is your 'Template' sheet, it contains the various YSL functions used to look up values from the Yardi DB at run-time. The 'message' sheet is just something displayed on the screeen while the report is processed. And the 'Summary' sheet is a sepearte sheet not touched by YSL at any point. The Summary sheet is something you have that summarizes data that will appear on the 'Report' worksheet which in this example will be named 'BalSht' (becasue T-BalSht is duplicated and named as BalSht). Any cells on the 'Summary' sheet that need to refernece the data returned by the YSL functions need to reference the cells on the 'Report' sheet (which would be named BalSht) and not the 'Template' whcih is 'T-BalSht'.

4)Now in order to reference any cells on the 'Report' sheet you will first need a report sheet. Do not just create a new sheet and name it BalSht but insetad before setting up any cross sheet references on the 'Summary' sheet, execute the report in Yardi-YSL and let the program create a Report sheet. Save the XLS file and then close Excel. Next open the XLs file up and then set up on your 'Summary' sheet the cell references to the 'Report' sheet which in this example would be named BalSht.

5) Before you try running the report again there is one change you will need to make to the references you have on the 'Summary' worksheet. As they are setup now, when you nexxt run the report in yardi, every reference on the 'Summary' sheet is going to be repalced with #Ref by Excel, not by YSL. This is because YSl will delete the sheet named 'BalSht' before creating a fresh duplicate of the 'template' (which is T-BalSht). This is jusst how YSl works. In order to stop Excel from automatically converting those cell refs to #Ref you will need to wrap each reference within Excel's Indirect function like this:

=INDIRECT("BalSht!C10")

Usiing Inddirect() on every cell refernce does require a lot of manual work buut once it's done you will not need to worry abot any more reference problems unless you change the structure/layout of your Template.

Good Luck
The YSL Guru

Why not change your sheet names to match the corrupted version? Is is possible to name your tabs such that the other software will not change the tab names.