Copying formulas on worksheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is an elementary question, as I am a new user. I am trying to set up a workbook of worksheets that will track cummulative totals as I add daily totals.
I have the formulas entered, but when I copy to the next sheet, I have to renew the formulas to go to the previous sheet. Sheet 2 goes to sheet one totals and adds the day, sheet 3, goes to 2. Is there a way that I do not have to redo formulas with each new sheet? I cannot seem to find the section in the information books to cover this.
 
ps,

That is one 3-D weakness of Excel that has yet to be addressed, so you are
not alone. You could use a User-Defined-Function (which requires some
advanced skills, so I won't recommend it to a new user), or use an INDIRECT
function with a reference to a cell on the sheet that contains the previous
sheet's name.

You can also select the cell and use replace to update the sheet reference,
rather than editing the formula.

HTH,
Bernie
MS Excel MVP

psquest said:
This is an elementary question, as I am a new user. I am trying to set up
Click to expand...
a workbook of worksheets that will track cummulative totals as I add daily
totals.
I have the formulas entered, but when I copy to the next sheet, I have to
Click to expand...
renew the formulas to go to the previous sheet. Sheet 2 goes to sheet one
totals and adds the day, sheet 3, goes to 2. Is there a way that I do not
have to redo formulas with each new sheet? I cannot seem to find the
section in the information books to cover this.
 
Bernie Deitrick wrote...
That is one 3-D weakness of Excel that has yet to be
addressed, so you are not alone. . . .
Click to expand...

You phrase it as though there's some, possibly remote, hope this wil
be addressed some day. Microsoft hasn't managed to do this for over 1
years (since Excel 4, or 15 years since 123 Release 3 came out), s
it's not obvious they intend ever to do so.
. . . You could use a User-Defined-Function (which requires some
advanced skills, so I won't recommend it to a new user), or use
an INDIRECT function with a reference to a cell on the sheet
that contains the previous sheet's name.
Click to expand...
...

Easier to enter a list of worksheet names in order in a range. Fo
example, if the worksheets, in order, were named Able, Baker, Charlie
Delta, then enter

Able
Baker
Charlie
Delta

in, say, X91:X94, name that range WSList (select that range, run th
menu command Insert > Name > Define Name, enter WSList in the Nam
field and click OK). Also add the following defined name - WSNam
referring to

=MID(CELL("Filename",INDIRECT("A1")),FIND("]",
CELL("Filename",INDIRECT("A1")))+1,32)

Then pull values from corresponding cells on previous worksheets usin
the formula

=INDIRECT("'"&INDEX(WSList,MATCH(WSName,WSList,0)-1)&"'!RC",0
 
Back
Top