scanning whole workbook with many spreadsheets

[Chris]

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

 

[ShaneDevenshire]

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(Janec!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!A17, 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.