formula help

  • Thread starter Thread starter James Beam
  • Start date Start date
J

James Beam

In a workbook of 31 pages. I am trying to add a totals page that will copy
the amount from one cell on each page. When trying to auto fill this formula
in a column, Excel will not automatically change the page numbers?
='1'!G$7*-1. Is there a way to make excel change the page numbers?
='1'!G$7*-1, ='2'!G$7*-1, ='3'!G$7*-1 etc.
 
Hi James!

You could use a couple of helper columns

Column A contains Sheet1 and will copy down incrementing the number
Column B contains the cell address and can copy down without the
number changing

Then you can use the formula in C:
=INDIRECT("'"&A1&"'!"&B1)*-1

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
James

I believe you mean "worksheets" when you refer to "pages".

Couple of methods.........

First......To sum all the G7 cells from all the sheets enter this formula in
your summary sheet, assuming it is the first sheet in the workbook.

=SUM(Sheet1:Sheet31!G7)

If your sheets are not in the 1,2,3 order, insert a new sheet to the right of
your Summary sheet. Name it Start. Insert a sheet at the end of your sheets.
Name it End.

In Summary sheet enter =SUM(Start:End!G7)

Second.........

If your sheets are numbered 1 through 31 and you want to pull G7 from each to
the Summary sheet enter this formula in A1 of the Summary sheet.

=INDIRECT(ROW()& "!G7")

or if Sheet1, Sheet2 etc =INDIRECT("Sheet" & (ROW() & "!G7") entered in A1 of
Summary sheet and dragged down 31 rows.

Gord Dibben Excel MVP
 
Back
Top