If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
link data from several speadsheets
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!C2 6+JAN12!C26+JAN12!C26+JAN13!C26+JAN14!C26+JAN15!C2 6+JAN16!C26+JAN17!C26+JAN18!C26+JAN19!C26+JAN20!C2 6+JAN21!C26+JAN22!C26+JAN23!C26+JAN24!C26+JAN25!C2 6+JAN26!C26+JAN27!C26+JAN28!C26+JAN29!C26+JAN30!C2 6+JAN31!C26 It did work on a smaller scale on a practice file. But not on this file. |
#2
|
|||
|
|||
link data from several speadsheets
Sherry S wrote:
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!C2 6+JAN12!C26+JAN12!C26+JAN13!C26+JAN14!C26+JAN15!C2 6+JAN16!C26+JAN17!C26+JAN18!C26+JAN19!C26+JAN20!C2 6+JAN21!C26+JAN22!C26+JAN23!C26+JAN24!C26+JAN25!C2 6+JAN26!C26+JAN27!C26+JAN28!C26+JAN29!C26+JAN30!C2 6+JAN31!C26 It did work on a smaller scale on a practice file. But not on this file. 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. |
#3
|
|||
|
|||
link data from several speadsheets
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 --------------- Jacob Skaria "Sherry S" wrote: 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!C2 6+JAN12!C26+JAN12!C26+JAN13!C26+JAN14!C26+JAN15!C2 6+JAN16!C26+JAN17!C26+JAN18!C26+JAN19!C26+JAN20!C2 6+JAN21!C26+JAN22!C26+JAN23!C26+JAN24!C26+JAN25!C2 6+JAN26!C26+JAN27!C26+JAN28!C26+JAN29!C26+JAN30!C2 6+JAN31!C26 It did work on a smaller scale on a practice file. But not on this file. |
#4
|
|||
|
|||
link data from several speadsheets
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. On Apr 26, 4:47*am, Jacob Skaria wrote: 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 --------------- Jacob Skaria "Sherry S" wrote: 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!C2 6+JAN12!C26+JAN12!C26+JAN13!C26+JAN14!C26+JAN15!C2 6+JAN16!C26+JAN17!C26+JAN18!C26+JAN19!C26+JAN20!C2 6+JAN21!C26+JAN22!C26+JAN23!C26+JAN24!C26+JAN25!C2 6+JAN26!C26+JAN27!C26+JAN28!C26+JAN29!C26+JAN30!C2 6+JAN31!C26 It did work on a smaller scale on a practice file. But not on this file.. |
Thread Tools | |
Display Modes | |
|
|