If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"='sheet 1'!D4" auto fill sheet to sheet ='sheet 2'!D4
how do auto fill formula ='sheet 1'!D4 to sheet 2 ='sheet 2'!D4
with out change the formula maunually I want to make one sheet with the formula and add sheets and have the new sheet look at the adjacent sheet etc |
#2
|
|||
|
|||
"='sheet 1'!D4" auto fill sheet to sheet ='sheet 2'!D4
You can use the INDIRECT function to do this. Note that this will work only
with sheets that have a name of SheetN, where N is some number. It will *not* work if the sheet have other names. Enter the following formula in some cell, and change the value N in the formula to the row number in which it was first entered. =INDIRECT("'Sheet"&ROW()-N+1&"'!D4") Now, fill down for as many cells as you have sheets. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "mistewalker" wrote in message ... how do auto fill formula ='sheet 1'!D4 to sheet 2 ='sheet 2'!D4 with out change the formula maunually I want to make one sheet with the formula and add sheets and have the new sheet look at the adjacent sheet etc |
#3
|
|||
|
|||
"='sheet 1'!D4" auto fill sheet to sheet ='sheet 2'!D4
On Jan 6, 8:19 pm, mistewalker
wrote: how do auto fill formula ='sheet 1'!D4 to sheet 2 ='sheet 2'!D4 with out change the formula maunually I want to make one sheet with the formula and add sheets and have the new sheet look at the adjacent sheet etc You could use the indirect function. Put this in row 1 your formula sheet and copy down: =INDIRECT("Sheet" &ROW() &"!D4") Per Erik |
#4
|
|||
|
|||
"='sheet 1'!D4" auto fill sheet to sheet ='sheet 2'!D4
i actually have the sheets named as dates "1-7-08" as an example and i just want to be able to make a new sheet every month and have the formula change to look at the previous sheet no matter what the name make sense? |
#5
|
|||
|
|||
"='sheet 1'!D4" auto fill sheet to sheet ='sheet 2'!D4
You could use Find & Replace to change the old sheet name to the new
sheet name. Hope this helps. Pete On Jan 6, 7:56*pm, mistewalker wrote: i actually have the sheets named as dates "1-7-08" as an example and i just want to be able to make a new sheet every month and have the formula change to look at the previous sheet no matter what the name make sense? |
#6
|
|||
|
|||
"='sheet 1'!D4" auto fill sheet to sheet ='sheet 2'!D4
You could use a User Defined Function to refer to the previous sheet.
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(D4) entered in a cell on sheet 1-7-08 will return value from D4 on sheet 1-6-08 Gord Dibben MS Excel MVP On Sun, 6 Jan 2008 11:56:00 -0800, mistewalker wrote: i actually have the sheets named as dates "1-7-08" as an example and i just want to be able to make a new sheet every month and have the formula change to look at the previous sheet no matter what the name make sense? |
Thread Tools | |
Display Modes | |
|
|