relative sheet referencing

[Glenn]

I'm stumped!!

In Excel 2000:

One workbook, starting with 2 "master" sheets (used as templates for
additional sheets)


TEMPlocation
TEMPsummary (with formulas that reference TEMPlocation!)

These sheets are normally hidden.

Upon user opening for for the first time:

I have a macro that allows user to input 4 char "location" code, then the
macro copies and renames two NEW sheets (concatenating the code) as follows:

locCode = inputbox...
Sheets("TEMPlocation").Visible = True
Sheets("TEMPlocation").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = locCode & "location"

Sheets("TEMPsummary").Visible = True
Sheets("TEMPsummary").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = locCode & "summary"

Upon reviewing the new location sheets, I notice the formulas in LOC1summary
still reference the original "TEMPlocation" sheet instead of the
"LOC1location" sheet.

I understand if I RENAME the sheets, Excel will update the formulas, but
I'll lose my template files for future additions.

I've attepted many different scenarios of copying, then renaming but I can't
seem to save the original sheets, and make new sheets that Excel will update
references to each other.

My closest solution has been by "hardcoding a reference" by creating a named
range (cell) that stores the text location code, then reference that cell
using the INDIRECT function to create the formula sheet reference (as
follows):

Cell A1 (value): LOC1

Formula cell B2 on same sheet: =INDIRECT(TRIM($A$1) & "location!L73")

resulting in (formula): =LOC1location!L73

This works - sort of....

The concern here is that the cell reference on the target sheet is absolute,
and if rows/columns are added/deleted this will kill the formula.

Is there a way to make the target cell reference (which is on a different
sheet) relative?

I also attempted to create a function passing in target cell reference
(cleaner - but still gets processed as absolute by excel).

OR

Is there a better way to copy / rename the sheets originally so that Excel
simply updates the formula references?

Thanks in advance for you consideration!

Regards,
Glenn

 

[Guest]

when you create the summary sheet, place the source sheet name
into Range("A1")
eg
Worksheets(locCode & "Summary").Range("A1")=locCode & "Location"
If you change the absolute references in the template to use the INDIRECT()
function, referenceing the cell A1, then your summary will work so long as A1
has the name of a worksheet.