link data from several speadsheets

S

Sherry S

Hi all,
I am trying to learn this as I go.

I have 31 speadsheets all in the same workbook to represent the days in the
month of January. They are exactly the same except the dates. I want to take
the grand totals from each one and place it on #32 sheet for an end of the
month total. The way i have the formula written right now gives me a #ref
error.

=JAN1!C26+JAN2!C26+JAN3!C26+JAN4!C26+JAN5!C26+JAN6!C26+JAN7!C26+JAN8!C26+JAN9!C26+JAN10!C26+JAN11!C26+JAN12!C26+JAN12!C26+JAN13!C26+JAN14!C26+JAN15!C26+JAN16!C26+JAN17!C26+JAN18!C26+JAN19!C26+JAN20!C26+JAN21!C26+JAN22!C26+JAN23!C26+JAN24!C26+JAN25!C26+JAN26!C26+JAN27!C26+JAN28!C26+JAN29!C26+JAN30!C26+JAN31!C26

It did work on a smaller scale on a practice file. But not on this file.
 
S

smartin

Sherry said:
Hi all,
I am trying to learn this as I go.

I have 31 speadsheets all in the same workbook to represent the days in the
month of January. They are exactly the same except the dates. I want to take
the grand totals from each one and place it on #32 sheet for an end of the
month total. The way i have the formula written right now gives me a #ref
error.

=JAN1!C26+JAN2!C26+JAN3!C26+JAN4!C26+JAN5!C26+JAN6!C26+JAN7!C26+JAN8!C26+JAN9!C26+JAN10!C26+JAN11!C26+JAN12!C26+JAN12!C26+JAN13!C26+JAN14!C26+JAN15!C26+JAN16!C26+JAN17!C26+JAN18!C26+JAN19!C26+JAN20!C26+JAN21!C26+JAN22!C26+JAN23!C26+JAN24!C26+JAN25!C26+JAN26!C26+JAN27!C26+JAN28!C26+JAN29!C26+JAN30!C26+JAN31!C26

It did work on a smaller scale on a practice file. But not on this file.
Click to expand...

A couple thoughts.

First, see if this technique works on your #32 sheet. Sum column B for
the final answer:

A B
Sheet SubTotal
1 =INDIRECT("JAN"&A2&"!C26")
2 ...fill down...
etc.


Other thought is, if all the worksheets "are exactly the same except the
dates" you could spare a lot of effort by consolidating the data onto
one worksheet with an added column for the date. This makes things like
getting monthly totals much simpler.
 
J

Jacob Skaria

It looks like one of your reference is invalid. Please try this

1. Check whether all sheets are present..
2. Within the sheet tab check for any spaces in front or after the sheet
names..


If this post helps click Yes
 
A

AB

You could easily solve it with a three dimensional formula, i.e., if
you need to add up range C26 from an array of worksheets/spreadsheets
- enter this (in any cell you want) in your #32 tab (or any other tab
for that matter):

=SUM(JAN1:JAN31!C26)

and what it'll do is it will add up all the 'C26' ranges of all the
worksheets between (inclusive) worksheets JAN1 and JAN31. So, the
important thing is WHERE the spreadsheet is and not HOW IT'S CALLED -
so, any spreadsheet located between the JAN1 and JAN31 worksheets
would be added up (range C26, obviously) - you just need to be careful
and not to move the worksheets around (i.e, do not move them before or
after JAN1 & JAN31).
I hope it helps.

A.
 
Top