Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not as stupid a question; this time about data from multiple sheet
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Not as stupid a question; this time about data from multiple sheet
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match and Sort for two range of data on different worksheets? | New Users to Excel | |||
Importing Data | Excel Worksheet Functions | |||
Newbie question Pulling data from one sheet to another based on | New Users to Excel | |||
Using a relative SHEET reference for source data in a chart | Charts and Charting in Excel | |||
Question about combining data from multiple workbooks into one rep | Excel Discussion (Misc queries) |