Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
scanning whole workbook with many spreadsheets
Hi all,
Got another problem: I want to create a formula so it can scan all worksheets within a workbook. For example, I have the tabs labeled Sept08 August08 July08 And within each tab, I have the same column headings Measures CaseMan Psychoed Beneath these column headings are either a 1 or 0 However, each Row within each sheet has a specific ID number. So for example: ID Measures CaseMan Psychoed 1 1 0 0 2 0 1 1 3 1 1 1 4 0 0 0 I want to sum each column heading with its corresponding ID number across the tabs listed above on a different worksheet within the same workbook. Does that make sense? Any suggestions? Thanks, Chris |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
scanning whole workbook with many spreadsheets
Hi,
The titles at the tops are the same, but are there the same number of rows (ID's) each month? I will assume not: Note: If the structure of each worksheet was the same, that means that there were always the same number of ID's in the same order then you could use a simple formula =SUM(Jan:Dec!B2) And this formula could be copied down and across on the summary sheet. Here is an entirely different approach: 1. On the Summary sheet place your cursor wherever you want the summary created, I assume the sheet has no titles. 2. Choose the command Data, Consolidate 3. With the References box selected, click the tab for your first month and highlight the data plus the titles, something like Jan!A1:D7, for example. 4. Click the Add button. The will add the range to the All References window. 5. Repeat steps 3 and 4 for each sheet. 6. Check Use labels in Top row, and Left column 7. Click OK. This creates a hard coded summary table. If you want it to be dynamic - meaning it will update if you change values (but not if you add new rows), the you should also check the box Create links to source data. if this helps, please click the Yes button. -- Thanks, Shane Devenshire "Chris" wrote: Hi all, Got another problem: I want to create a formula so it can scan all worksheets within a workbook. For example, I have the tabs labeled Sept08 August08 July08 And within each tab, I have the same column headings Measures CaseMan Psychoed Beneath these column headings are either a 1 or 0 However, each Row within each sheet has a specific ID number. So for example: ID Measures CaseMan Psychoed 1 1 0 0 2 0 1 1 3 1 1 1 4 0 0 0 I want to sum each column heading with its corresponding ID number across the tabs listed above on a different worksheet within the same workbook. Does that make sense? Any suggestions? Thanks, Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maximunm # of Spreadsheets in any Workbook | Excel Discussion (Misc queries) | |||
Maximunm # of Spreadsheets in any Workbook | New Users to Excel | |||
Linking Spreadsheets in a Workbook | Excel Discussion (Misc queries) | |||
Linking Spreadsheets within a workbook | Excel Discussion (Misc queries) | |||
different datestamps for different spreadsheets in a workbook | Excel Discussion (Misc queries) |