Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference a cell to another in the preceding worksheet
When I create another worksheet (ie copy a sheet) I want to refer in the new
worksheet to some cell values in the PRECEDING worksheet. How do I refer to that sheet in the formulae so that it always refers to the previous sheet. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference a cell to another in the preceding worksheet
If you wanted to reference A1 in Sheet3 of the same workbook:
=Sheet3!A1 If you wanted to reference A1 in Sheet3 of Book3.xls: =[Book3.xls]Sheet3!A1 I hope that helps "karl.sonja" wrote: When I create another worksheet (ie copy a sheet) I want to refer in the new worksheet to some cell values in the PRECEDING worksheet. How do I refer to that sheet in the formulae so that it always refers to the previous sheet. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference a cell to another in the preceding worksheet
In front of each cell reference add the sheet name. So for the following:
=A1/A3 will change to: ='New SheetName'!A1/'New Sheetname'!A3 Hope that helps, Jim -- I appreciate any feedback. Please don''t be scared to say that "Yes" I/someone else did answer your question. Thank you. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference a cell to another in the preceding workshee
Jim's post makes a great point. If the name of your sheet has spaces you
need to enclose it with quotes: =ThisSheet!A3 vs ='This Sheet'!A3 "J Sedoff" wrote: In front of each cell reference add the sheet name. So for the following: =A1/A3 will change to: ='New SheetName'!A1/'New Sheetname'!A3 Hope that helps, Jim -- I appreciate any feedback. Please don''t be scared to say that "Yes" I/someone else did answer your question. Thank you. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference a cell to another in the preceding worksheet
What is the naming routine of your sheets.
It's much easier to automate something like this if they're numbered in some fashion. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "karl.sonja" wrote in message ... When I create another worksheet (ie copy a sheet) I want to refer in the new worksheet to some cell values in the PRECEDING worksheet. How do I refer to that sheet in the formulae so that it always refers to the previous sheet. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference a cell to another in the preceding workshee
I will give you my example of a cell ='SV 122'!N43
My sheets are named SV 120, SV 121, SV 122 etc When I create sheet SV 122 by copying SV 121 I have to manually edit ='SV 121'!N43 to ='SV 122'!N43. This is what I want to avoid doing because I have a large number of cells to edit each time I create the next sheet. Is there a way I can increment automatically the 121 to 122 during the copy/create a new sheet process? "RagDyer" wrote: What is the naming routine of your sheets. It's much easier to automate something like this if they're numbered in some fashion. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "karl.sonja" wrote in message ... When I create another worksheet (ie copy a sheet) I want to refer in the new worksheet to some cell values in the PRECEDING worksheet. How do I refer to that sheet in the formulae so that it always refers to the previous sheet. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference a cell to another in the preceding workshee
"karl.sonja" wrote in message ... When I create another worksheet (ie copy a sheet) I want to refer in the new worksheet to some cell values in the PRECEDING worksheet. How do I refer to that sheet in the formulae so that it always refers to the previous sheet. "RagDyer" wrote: What is the naming routine of your sheets. It's much easier to automate something like this if they're numbered in some fashion. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- karl.sonja wrote: I will give you my example of a cell ='SV 122'!N43 My sheets are named SV 120, SV 121, SV 122 etc When I create sheet SV 122 by copying SV 121 I have to manually edit ='SV 121'!N43 to ='SV 122'!N43. This is what I want to avoid doing because I have a large number of cells to edit each time I create the next sheet. Is there a way I can increment automatically the 121 to 122 during the copy/create a new sheet process? If you want to replace all references to 'SV 121' with 'SV 122' then select the columns, rows or the whole sheet (as needed) and use "Find and Replace". |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference a cell to another in the preceding workshee
Thanks. I wanted to rate your reply but could not find the button to do sa
"Glenn" wrote: "karl.sonja" wrote in message ... When I create another worksheet (ie copy a sheet) I want to refer in the new worksheet to some cell values in the PRECEDING worksheet. How do I refer to that sheet in the formulae so that it always refers to the previous sheet. "RagDyer" wrote: What is the naming routine of your sheets. It's much easier to automate something like this if they're numbered in some fashion. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- karl.sonja wrote: I will give you my example of a cell ='SV 122'!N43 My sheets are named SV 120, SV 121, SV 122 etc When I create sheet SV 122 by copying SV 121 I have to manually edit ='SV 121'!N43 to ='SV 122'!N43. This is what I want to avoid doing because I have a large number of cells to edit each time I create the next sheet. Is there a way I can increment automatically the 121 to 122 during the copy/create a new sheet process? If you want to replace all references to 'SV 121' with 'SV 122' then select the columns, rows or the whole sheet (as needed) and use "Find and Replace". |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference a cell to another in the preceding worksheet
Karl
If you're willing to use a User Defined Function which ignores sheet names. Function PrevSheet(rg As Range) n = Application.Caller.Parent.Index If n = 1 Then PrevSheet = CVErr(xlErrRef) ElseIf TypeName(Sheets(n - 1)) = "Chart" Then PrevSheet = CVErr(xlErrNA) Else PrevSheet = Sheets(n - 1).Range(rg.Address).Value End If End Function =PrevSheet(F14) =SUM(A1:A10) + Prevsheet(F14) Gord Dibben MS Excel MVP On Tue, 5 Aug 2008 12:18:00 -0700, karl.sonja wrote: When I create another worksheet (ie copy a sheet) I want to refer in the new worksheet to some cell values in the PRECEDING worksheet. How do I refer to that sheet in the formulae so that it always refers to the previous sheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My formula cell has stopped adding the preceding cells. | Excel Worksheet Functions | |||
copying sheet references that refer to a cell in the preceding she | Excel Worksheet Functions | |||
Need Excel Function that will return # of char in preceding cell | Excel Discussion (Misc queries) | |||
Need Excel Function that will return # of char in preceding cell | Excel Worksheet Functions | |||
Worksheet reference (i.e placing worksheet name in a cell) | Excel Worksheet Functions |