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

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.




"Glenn" wrote:

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