Not as stupid a question; this time about data from multiple sheet

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

Guest

Yeah, array function; control+shift+enter. I got it as soon as I hit send.

What I want to do this is add a report page to the front of a workbook that
will summarize the information from the next six pages. The thing is the
next six pages are monthly reports so each month a new page is added right
under the front page.

Right now there is the Six Month Report (11/05-04/06) but at the end of this
month a new page will be added and the Six Month Report will be for
(12/05-05/06).

The report is an exact replica of the monthly reports except for the data.

Is there a way to write the formulae to take the next six sheets without
specifically naming what those sheets are?

Ben Karlin
St. Louis, MO
 
Assuming your sheet names are in the form Jan_06, Feb_06, Mar_06 etc
and you are just about to add a May_06 sheet, then currently your
formulae in the Six Month Report will be something like (for cell C5):

=SUM(Apr_06:Nov_05!C5)

When you add a new sheet (assuming it is the second sheet and the
others are in reverse sequence), then you could just do a Find and
Replace twice. Highlight all the cells in the Six Month Report sheet
(CTRL-A), then Edit | Replace (or CTRL-H) and:

Find What: Nov_05
Replace With: Dec_05

then click Replace All. Do CTRL-H again, and:

Find What: Apr_06
Replace With: May_06

and click Replace All.

This is a procedural rather than a formula solution, but it should
work.

If, instead, your formulae are in this format:

=Nov_05!C5 + Dec_05!C5 + Jan_06!C5 + Feb_06!C5 + Mar_06!C5 + Apr_06!C5

then you just have to replace Nov_05 with May_06, and the ordering of
the sheets doesn't matter.

Hope this helps.

Pete
 
Back
Top