Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
relative sheet referencing - what's the secret?
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? 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
relative sheet referencing - what's the secret?
The secret is not obvious, but it is simple: select the two sheets together
and copy them at the same time: Public Sub CopyTogether(S1 As Worksheet, S2 As Worksheet) Dim C As Integer C = Worksheets.Count Sheets(Array(S1.Name, S2.Name)).Copy After:=Sheets(C) End Sub -- - K Dales "Glennster" 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? 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing Relative Sheets | Excel Worksheet Functions | |||
Relative worksheet referencing | Excel Discussion (Misc queries) | |||
Relative sheet referencing in excel formulas | Excel Worksheet Functions | |||
Relative Cell Referencing | Excel Programming | |||
Relative referencing | Excel Programming |