Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master sheet
Because no two people have the same file format or requirements, there really
isn't a single solution to fit all needs.
HOWEVER:
IF all of the data sheets are in the same format and
IF all of the data sheets have all entries
Basically, if the value for "Cola" "Op STK" is always in cell C2
or even more basic:
each of your 31 sheets is made from the same "template"
so that all ITEMS are listed in the same row on all sheets..
Then, here's what I did in Excel 2010.
(I THINK it works in earlier versions)
First, make sure your "Consolidated" sheet is either the first or the last
sheet
of your workbook.
In your "Consolidated" sheet, select cell C7 (Cola, Op Stk)
type =sum(
then select the first sheet (01)
hold down the shift key and select the last sheet (05 in the sample)
(Be sure to NOT include the Consolidated sheet)
This will input:
=sum('01:05'!
in the cell
Your first sheet should be displayed (01)
click in the "Cola, Op STK" cell (C2)
then type a closing parenthesis.
You should end up with:
=SUM('01:05'!C2)
This will sum all of contents of cell C2 in each of your sheets.
copy/drag this formula across the columns, then down the rows
and you should have your consolidation sheet.
let me know how it works.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-
From: Rajesh thrissur
To: excel-macros
Sent: Tue, January 22, 2013 7:36:40 AM
Subject: $$Excel-Macros$$ Consolidate data from 31 sheets to one master sheet
Hi Experts,
Is there any method to consolidate data from many worksheets to one
master worksheet, here I attached a simple work book it have only 5
data sheets and 1 master sheet. being data is very less I solve it
with just adding data from reference cells but when data is more this
method is not practical. If there is any special method pls advise me.
with Regards
Rajesh Kainikkara
--
Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
--
Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master sheet
Hi Paul
Thanks for the idea you gave. Is there any way to add date criteria also
there eg: 01/01/13 to 05/01/13 or 01/01/13 to 10/01/13.
With regards and thanks
Rajesh kainikkara
On Tuesday, January 22, 2013 6:28:14 PM UTC+5:30, Paul Schreiner wrote:
>
> Because no two people have the same file format or requirements, there
> really isn't a single solution to fit all needs.
>
> HOWEVER:
>
> IF all of the data sheets are in the same format and
> IF all of the data sheets have all entries
> Basically, if the value for "Cola" "Op STK" is always in cell C2
> or even more basic:
>
> each of your 31 sheets is made from the same "template"
> so that all ITEMS are listed in the same row on all sheets..
>
> Then, here's what I did in Excel 2010.
> (I THINK it works in earlier versions)
>
> First, make sure your "Consolidated" sheet is either the first or the last
> sheet of your workbook.
>
> In your "Consolidated" sheet, select cell C7 (Cola, Op Stk)
> type =sum(
> then select the first sheet (01)
> hold down the shift key and select the last sheet (05 in the sample)
> (Be sure to NOT include the Consolidated sheet)
>
> This will input:
> =sum('01:05'!
> in the cell
> Your first sheet should be displayed (01)
> click in the "Cola, Op STK" cell (C2)
>
> then type a closing parenthesis.
>
> You should end up with:
> =SUM('01:05'!C2)
>
> This will sum all of contents of cell C2 in each of your sheets.
> copy/drag this formula across the columns, then down the rows
> and you should have your consolidation sheet.
>
> let me know how it works.
>
>
>
> *Paul*
>
> -
> *“Do all the good you can,
> By all the means you can,
> In all the ways you can,
> In all the places you can,
> At all the times you can,
> To all the people you can,
> As long as ever you can.” - John Wesley
> *-
>
>
> --
> *From:* Rajesh thrissur >
> *To:* excel-macros >
> *Sent:* Tue, January 22, 2013 7:36:40 AM
> *Subject:* $$Excel-Macros$$ Consolidate data from 31 sheets to one master
> sheet
>
> Hi Experts,
>
> Is there any method to consolidate data from many worksheets to one
> master worksheet, here I attached a simple work book it have only 5
> data sheets and 1 master sheet. being data is very less I solve it
> with just adding data from reference cells but when data is more this
> method is not practical. If there is any special method pls advise me.
>
> with Regards
>
> Rajesh Kainikkara
>
> --
> Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To post to this group, send email to excel-...@googlegroups.com
> .
> To unsubscribe from this group, send email to excel-macros+
> unsub...@googlegroups.com .
> Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>
>
>
--
Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master sheet
how do you identify "date criteria"?
is it by sheet name?
I don't see the dates on any of the individual sheets.
I'm pretty sure we can't include a date parameter with a simple function.
We'd have to write some VBA to do it.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-
From: kasper
To: excel-macros@googlegroups.com
Sent: Tue, January 22, 2013 8:13:42 AM
Subject: Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master
sheet
Hi Paul
Thanks for the idea you gave. Is there any way to add date criteria also there
eg: 01/01/13 to 05/01/13 or 01/01/13 to 10/01/13.
With regards and thanks
Rajesh kainikkara
On Tuesday, January 22, 2013 6:28:14 PM UTC+5:30, Paul Schreiner wrote:
Because no two people have the same file format or requirements, there really
isn't a single solution to fit all needs.
>
>HOWEVER:
>
>IF all of the data sheets are in the same format and
>IF all of the data sheets have all entries
>
>Basically, if the value for "Cola" "Op STK" is always in cell C2
>or even more basic:
>
>each of your 31 sheets is made from the same "template"
>so that all ITEMS are listed in the same row on all sheets..
>
>Then, here's what I did in Excel 2010.
>(I THINK it works in earlier versions)
>
>First, make sure your "Consolidated" sheet is either the first or the last
>sheet
>of your workbook.
>
>In your "Consolidated" sheet, select cell C7 (Cola, Op Stk)
>type =sum(
>then select the first sheet (01)
>hold down the shift key and select the last sheet (05 in the sample)
>(Be sure to NOT include the Consolidated sheet)
>
>This will input:
>=sum('01:05'!
>in the cell
>Your first sheet should be displayed (01)
>click in the "Cola, Op STK" cell (C2)
>
>then type a closing parenthesis.
>
>You should end up with:
>=SUM('01:05'!C2)
>
>This will sum all of contents of cell C2 in each of your sheets.
>copy/drag this formula across the columns, then down the rows
>and you should have your consolidation sheet.
>
>let me know how it works.
>
>
>Paul
>-- ---
>“Do all the good you can,
>By all the means you can,
>In all the ways you can,
>In all the places you can,
>At all the times you can,
>To all the people you can,
>As long as ever you can.” - John Wesley
>-- ---
>
>
>
>
>
From: Rajesh thrissur
>To: excel-macros
>Sent: Tue, January 22, 2013 7:36:40 AM
>Subject: $$Excel-Macros$$ Consolidate data from 31 sheets to one master sheet
>
>Hi Experts,
>
>Is there any method to consolidate data from many worksheets to one
>master worksheet, here I attached a simple work book it have only 5
>data sheets and 1 master sheet. being data is very less I solve it
>with just adding data from reference cells but when data is more this
>method is not practical. If there is any special method pls advise me.
>
>with Regards
>
>Rajesh Kainikkara
>
>--
>Join official Facebook page of this forum @ https://www.facebook.com/
>discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To post to this group, send email to excel-...@googlegroups.com.
>To unsubscribe from this group, send email to excel-macros+unsub...@
>googlegroups.com.
>Visit this group at http://groups.google.com/ group/excel-macros?hl=en.
>
>
>--
Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master sheet
Hi Paul
Date is in the consolidation page there i give it under the heading from
01/01/13 to 05/01/13
Regards
Rajesh kainikkara
On Tuesday, January 22, 2013 7:03:13 PM UTC+5:30, Paul Schreiner wrote:
>
> how do you identify "date criteria"?
> is it by sheet name?
>
> I don't see the dates on any of the individual sheets.
>
> I'm pretty sure we can't include a date parameter with a simple function.
> We'd have to write some VBA to do it.
>
>
> *Paul*
>
> -
> *“Do all the good you can,
> By all the means you can,
> In all the ways you can,
> In all the places you can,
> At all the times you can,
> To all the people you can,
> As long as ever you can.” - John Wesley
> *-
>
>
> --
> *From:* kasper >
> *To:* excel-...@googlegroups.com
> *Sent:* Tue, January 22, 2013 8:13:42 AM
> *Subject:* Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one
> master sheet
>
> Hi Paul
>
> Thanks for the idea you gave. Is there any way to add date criteria also
> there eg: 01/01/13 to 05/01/13 or 01/01/13 to 10/01/13.
>
>
> With regards and thanks
>
> Rajesh kainikkara
>
> On Tuesday, January 22, 2013 6:28:14 PM UTC+5:30, Paul Schreiner wrote:
>>
>> Because no two people have the same file format or requirements, there
>> really isn't a single solution to fit all needs.
>>
>> HOWEVER:
>>
>> IF all of the data sheets are in the same format and
>> IF all of the data sheets have all entries
>> Basically, if the value for "Cola" "Op STK" is always in cell C2
>> or even more basic:
>>
>> each of your 31 sheets is made from the same "template"
>> so that all ITEMS are listed in the same row on all sheets..
>>
>> Then, here's what I did in Excel 2010.
>> (I THINK it works in earlier versions)
>>
>> First, make sure your "Consolidated" sheet is either the first or the
>> last sheet of your workbook.
>>
>> In your "Consolidated" sheet, select cell C7 (Cola, Op Stk)
>> type =sum(
>> then select the first sheet (01)
>> hold down the shift key and select the last sheet (05 in the sample)
>> (Be sure to NOT include the Consolidated sheet)
>>
>> This will input:
>> =sum('01:05'!
>> in the cell
>> Your first sheet should be displayed (01)
>> click in the "Cola, Op STK" cell (C2)
>>
>> then type a closing parenthesis.
>>
>> You should end up with:
>> =SUM('01:05'!C2)
>>
>> This will sum all of contents of cell C2 in each of your sheets.
>> copy/drag this formula across the columns, then down the rows
>> and you should have your consolidation sheet.
>>
>> let me know how it works.
>>
>>
>>
>> *Paul*
>>
>> -- ---
>> *“Do all the good you can,
>> By all the means you can,
>> In all the ways you can,
>> In all the places you can,
>> At all the times you can,
>> To all the people you can,
>> As long as ever you can.” - John Wesley
>> *-- ---
>>
>>
>> --
>> *From:* Rajesh thrissur
>> *To:* excel-macros
>> *Sent:* Tue, January 22, 2013 7:36:40 AM
>> *Subject:* $$Excel-Macros$$ Consolidate data from 31 sheets to one
>> master sheet
>>
>> Hi Experts,
>>
>> Is there any method to consolidate data from many worksheets to one
>> master worksheet, here I attached a simple work book it have only 5
>> data sheets and 1 master sheet. being data is very less I solve it
>> with just adding data from reference cells but when data is more this
>> method is not practical. If there is any special method pls advise me.
>>
>> with Regards
>>
>> Rajesh Kainikkara
>>
>> --
>> Join official Facebook page of this forum @ https://www.facebook.com/
>> discussexcel <https://www.facebook.com/discussexcel>
>>
>> FORUM RULES
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>> 2) Don't post a question in the thread of another member.
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>> 4) Acknowledge the responses you receive, good
Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master sheet
OK...
how does that affect the calculation?
you asked: "Is there any way to add date criteria".
but in your workbook, these dates do not seem to be a reference of any kind.
the phrase "add date criteria"
COULD mean that you want to add the values that are recorded within the date
range..
But your sheets don't have dates, so...
it COULD mean that you simply want to UPDATE these two dates.
but you've supplied no criteria to determine how these values are determined.
so.. what is it you want to do?
Please describe what you wish to accomplish.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-
From: kasper
To: excel-macros@googlegroups.com
Sent: Wed, January 23, 2013 9:40:29 AM
Subject: Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master
sheet
Hi Paul
Date is in the consolidation page there i give it under the heading from
01/01/13 to 05/01/13
Regards
Rajesh kainikkara
On Tuesday, January 22, 2013 7:03:13 PM UTC+5:30, Paul Schreiner wrote:
how do you identify "date criteria"?
>is it by sheet name?
>
>I don't see the dates on any of the individual sheets.
>
>I'm pretty sure we can't include a date parameter with a simple function.
>We'd have to write some VBA to do it.
>
>Paul
>-- ---
>“Do all the good you can,
>By all the means you can,
>In all the ways you can,
>In all the places you can,
>At all the times you can,
>To all the people you can,
>As long as ever you can.” - John Wesley
>-- ---
>
>
>
>
>
____________
From: kasper
>To: excel-...@googlegroups.com
>Sent: Tue, January 22, 2013 8:13:42 AM
>Subject: Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master
>sheet
>
>Hi Paul
>
>
>Thanks for the idea you gave. Is there any way to add date criteria also there
>eg: 01/01/13 to 05/01/13 or 01/01/13 to 10/01/13.
>
>
>
>
>With regards and thanks
>
>
>Rajesh kainikkara
>
>On Tuesday, January 22, 2013 6:28:14 PM UTC+5:30, Paul Schreiner wrote:
>Because no two people have the same file format or requirements, there really
>isn't a single solution to fit all needs.
>>
>>HOWEVER:
>>
>>IF all of the data sheets are in the same format and
>>IF all of the data sheets have all entries
>>
>>Basically, if the value for "Cola" "Op STK" is always in cell C2
>>or even more basic:
>>
>>each of your 31 sheets is made from the same "template"
>>so that all ITEMS are listed in the same row on all sheets..
>>
>>Then, here's what I did in Excel 2010.
>>(I THINK it works in earlier versions)
>>
>>First, make sure your "Consolidated" sheet is either the first or the last
>>sheet
>>of your workbook.
>>
>>In your "Consolidated" sheet, select cell C7 (Cola, Op Stk)
>>type =sum(
>>then select the first sheet (01)
>>hold down the shift key and select the last sheet (05 in the sample)
>>(Be sure to NOT include the Consolidated sheet)
>>
>>This will input:
>>=sum('01:05'!
>>in the cell
>>Your first sheet should be displayed (01)
>>click in the "Cola, Op STK" cell (C2)
>>
>>then type a closing parenthesis.
>>
>>You should end up with:
>>=SUM('01:05'!C2)
>>
>>This will sum all of contents of cell C2 in each of your sheets.
>>copy/drag this formula across the columns, then down the rows
>>and you should have your consolidation sheet.
>>
>>let me know how it works.
>>
>>
>>Paul
>>-- ---
>>“Do all the good you can,
>>By all the means you can,
>>In all the ways you can,
>>In all the places you can,
>>At all the times you can,
>>To all the people you can,
>>As long as ever you can.” - John Wesley
>>-- ---
>>
>>
>>
>>
>>
From: Rajesh thrissur
>>To: excel-macros
>>Sent: Tue, January 22, 2013 7:36:40 AM
>>Subject: $$Excel-Macros$$ Consolidate data from 31 sheets to one master sheet
>>
>>Hi Experts,
>>
>>Is there any method to consolidate data from many worksheets to one
>>master worksheet, here I attached a simple work book it have only 5
>>data sheets and
Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master sheet
You can use the Link worksheets together by:
Type the equal sign (=) in the C7 of Consolidate sheet, click the
sheet 01 and Click on C2 and then type a plus sign (+), Click on the
sheet 02 and Click on C2 and then type a plus sign (+) Click
on sheet 05 and click on C2 and press Enter, we have the following
formula:
='01'!C2+'02'!C2+'03'!C2+'04'!C2+'05'!C2
Next Click any C7 Fill down and horizontal Fill'll be results.
2013/1/23, Paul Schreiner :
> OK...
> how does that affect the calculation?
>
> you asked: "Is there any way to add date criteria".
>
> but in your workbook, these dates do not seem to be a reference of any
> kind.
>
>
> the phrase "add date criteria"
>
> COULD mean that you want to add the values that are recorded within the date
>
> range..
> But your sheets don't have dates, so...
> it COULD mean that you simply want to UPDATE these two dates.
> but you've supplied no criteria to determine how these values are
> determined.
>
> so.. what is it you want to do?
> Please describe what you wish to accomplish.
>
> Paul
> -
> “Do all the good you can,
> By all the means you can,
> In all the ways you can,
> In all the places you can,
> At all the times you can,
> To all the people you can,
> As long as ever you can.” - John Wesley
> -------------
>
>
>
>
> ________
> From: kasper
> To: excel-macros@googlegroups.com
> Sent: Wed, January 23, 2013 9:40:29 AM
> Subject: Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master
>
> sheet
>
> Hi Paul
>
> Date is in the consolidation page there i give it under the heading from
> 01/01/13 to 05/01/13
>
> Regards
> Rajesh kainikkara
>
> On Tuesday, January 22, 2013 7:03:13 PM UTC+5:30, Paul Schreiner wrote:
> how do you identify "date criteria"?
>>is it by sheet name?
>>
>>I don't see the dates on any of the individual sheets.
>>
>>I'm pretty sure we can't include a date parameter with a simple function.
>>We'd have to write some VBA to do it.
>>
>>Paul
>>-- ---
>>“Do all the good you can,
>>By all the means you can,
>>In all the ways you can,
>>In all the places you can,
>>At all the times you can,
>>To all the people you can,
>>As long as ever you can.” - John Wesley
>>-- ---
>>
>>
>>
>>
>>
>
> From: kasper
>>To: excel-...@googlegroups.com
>>Sent: Tue, January 22, 2013 8:13:42 AM
>>Subject: Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master
>>
>>sheet
>>
>>Hi Paul
>>
>>
>>Thanks for the idea you gave. Is there any way to add date criteria also
>> there
>>eg: 01/01/13 to 05/01/13 or 01/01/13 to 10/01/13.
>>
>>
>>
>>
>>With regards and thanks
>>
>>
>>Rajesh kainikkara
>>
>>On Tuesday, January 22, 2013 6:28:14 PM UTC+5:30, Paul Schreiner wrote:
>>Because no two people have the same file format or requirements, there
>> really
>>isn't a single solution to fit all needs.
>>>
>>>HOWEVER:
>>>
>>>IF all of the data sheets are in the same format and
>>>IF all of the data sheets have all entries
>>>
>>>Basically, if the value for "Cola" "Op STK" is always in cell C2
>>>or even more basic:
>>>
>>>each of your 31 sheets is made from the same "template"
>>>so that all ITEMS are listed in the same row on all sheets..
>>>
>>>Then, here's what I did in Excel 2010.
>>>(I THINK it works in earlier versions)
>>>
>>>First, make sure your "Consolidated" sheet is either the first or the last
>>> sheet
>>>of your workbook.
>>>
>>>In your "Consolidated" sheet, select cell C7 (Cola, Op Stk)
>>>type =sum(
>>>then select the first sheet (01)
>>>hold down the shift key and select the last sheet (05 in the sample)
>>>(Be sure to NOT include the Consolidated sheet)
>>>
>>>This will input:
>>>=sum('01:05'!
>>>in the cell
>>>Your first sheet should be displayed (01)
>>>click in the "Cola, Op STK" cell (C2)
>>>
>>>then type a closing parenthesis.
>>>
>>>You should end up with:
>>>=SUM('01:05'!C2)
&g
Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master sheet
Hi Paul
It can be identified by sheet name.I am trying to solve it by formula only.
Regards
Rajesh
On 1/22/13, Paul Schreiner wrote:
> how do you identify "date criteria"?
> is it by sheet name?
>
> I don't see the dates on any of the individual sheets.
>
> I'm pretty sure we can't include a date parameter with a simple function.
> We'd have to write some VBA to do it.
>
> Paul
> -
> “Do all the good you can,
> By all the means you can,
> In all the ways you can,
> In all the places you can,
> At all the times you can,
> To all the people you can,
> As long as ever you can.” - John Wesley
> -
>
>
>
>
>
> From: kasper
> To: excel-macros@googlegroups.com
> Sent: Tue, January 22, 2013 8:13:42 AM
> Subject: Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master
>
> sheet
>
> Hi Paul
>
> Thanks for the idea you gave. Is there any way to add date criteria also
> there
> eg: 01/01/13 to 05/01/13 or 01/01/13 to 10/01/13.
>
>
> With regards and thanks
>
> Rajesh kainikkara
>
> On Tuesday, January 22, 2013 6:28:14 PM UTC+5:30, Paul Schreiner wrote:
> Because no two people have the same file format or requirements, there
> really
> isn't a single solution to fit all needs.
>>
>>HOWEVER:
>>
>>IF all of the data sheets are in the same format and
>>IF all of the data sheets have all entries
>>
>>Basically, if the value for "Cola" "Op STK" is always in cell C2
>>or even more basic:
>>
>>each of your 31 sheets is made from the same "template"
>>so that all ITEMS are listed in the same row on all sheets..
>>
>>Then, here's what I did in Excel 2010.
>>(I THINK it works in earlier versions)
>>
>>First, make sure your "Consolidated" sheet is either the first or the last
>> sheet
>>of your workbook.
>>
>>In your "Consolidated" sheet, select cell C7 (Cola, Op Stk)
>>type =sum(
>>then select the first sheet (01)
>>hold down the shift key and select the last sheet (05 in the sample)
>>(Be sure to NOT include the Consolidated sheet)
>>
>>This will input:
>>=sum('01:05'!
>>in the cell
>>Your first sheet should be displayed (01)
>>click in the "Cola, Op STK" cell (C2)
>>
>>then type a closing parenthesis.
>>
>>You should end up with:
>>=SUM('01:05'!C2)
>>
>>This will sum all of contents of cell C2 in each of your sheets.
>>copy/drag this formula across the columns, then down the rows
>>and you should have your consolidation sheet.
>>
>>let me know how it works.
>>
>>
>>Paul
>>-- ---
>>“Do all the good you can,
>>By all the means you can,
>>In all the ways you can,
>>In all the places you can,
>>At all the times you can,
>>To all the people you can,
>>As long as ever you can.” - John Wesley
>>-- ---
>>
>>
>>
>>
>>
>
> From: Rajesh thrissur
>>To: excel-macros
>>Sent: Tue, January 22, 2013 7:36:40 AM
>>Subject: $$Excel-Macros$$ Consolidate data from 31 sheets to one master
>> sheet
>>
>>Hi Experts,
>>
>>Is there any method to consolidate data from many worksheets to one
>>master worksheet, here I attached a simple work book it have only 5
>>data sheets and 1 master sheet. being data is very less I solve it
>>with just adding data from reference cells but when data is more this
>>method is not practical. If there is any special method pls advise me.
>>
>>with Regards
>>
>>Rajesh Kainikkara
>>
>>--
>>Join official Facebook page of this forum @ https://www.facebook.com/
>>discussexcel
>>
>>FORUM RULES
>>
>>1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help,
>>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not
>> get
>>quick attention or may not be answered.
>>2) Don't post a question in the thread of another member.
>>3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>4) Acknowledge the responses you receive, good or bad.
>>5) Jobs posting is not allowed.
>>6) Sharing copyrighted material and their links is not allowed.
>>
>>NOTE : Don't ever post confidential data in a workbook. Forum owners and
Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master sheet
Hi Thanks for the reply.I know this method but when data is more and fields not in same order it will not be practical is it possible to use any array formula for this. With Regards & Thanks Rajesh kainikkara On 1/24/13, Bé Trần Văn wrote: > You can use the Link worksheets together by: > Type the equal sign (=) in the C7 of Consolidate sheet, click the > sheet 01 and Click on C2 and then type a plus sign (+), Click on the > sheet 02 and Click on C2 and then type a plus sign (+) Click > on sheet 05 and click on C2 and press Enter, we have the following > formula: > ='01'!C2+'02'!C2+'03'!C2+'04'!C2+'05'!C2 > > Next Click any C7 Fill down and horizontal Fill'll be results. > > > > 2013/1/23, Paul Schreiner : >> OK... >> how does that affect the calculation? >> >> you asked: "Is there any way to add date criteria". >> >> but in your workbook, these dates do not seem to be a reference of any >> kind. >> >> >> the phrase "add date criteria" >> >> COULD mean that you want to add the values that are recorded within the >> date >> >> range.. >> But your sheets don't have dates, so... >> it COULD mean that you simply want to UPDATE these two dates. >> but you've supplied no criteria to determine how these values are >> determined. >> >> so.. what is it you want to do? >> Please describe what you wish to accomplish. >> >> Paul >> - >> “Do all the good you can, >> By all the means you can, >> In all the ways you can, >> In all the places you can, >> At all the times you can, >> To all the people you can, >> As long as ever you can.” - John Wesley >> - >> >> >> >> >> >> From: kasper >> To: excel-macros@googlegroups.com >> Sent: Wed, January 23, 2013 9:40:29 AM >> Subject: Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one >> master >> >> sheet >> >> Hi Paul >> >> Date is in the consolidation page there i give it under the heading from >> 01/01/13 to 05/01/13 >> >> Regards >> Rajesh kainikkara >> >> On Tuesday, January 22, 2013 7:03:13 PM UTC+5:30, Paul Schreiner wrote: >> how do you identify "date criteria"? >>>is it by sheet name? >>> >>>I don't see the dates on any of the individual sheets. >>> >>>I'm pretty sure we can't include a date parameter with a simple function. >>>We'd have to write some VBA to do it. >>> >>>Paul >>>-- --- >>>“Do all the good you can, >>>By all the means you can, >>>In all the ways you can, >>>In all the places you can, >>>At all the times you can, >>>To all the people you can, >>>As long as ever you can.” - John Wesley >>>-- --- >>> >>> >>> >>> >>> >> >> From: kasper >>>To: excel-...@googlegroups.com >>>Sent: Tue, January 22, 2013 8:13:42 AM >>>Subject: Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one >>> master >>> >>>sheet >>> >>>Hi Paul >>> >>> >>>Thanks for the idea you gave. Is there any way to add date criteria also >>> there >>>eg: 01/01/13 to 05/01/13 or 01/01/13 to 10/01/13. >>> >>> >>> >>> >>>With regards and thanks >>> >>> >>>Rajesh kainikkara >>> >>>On Tuesday, January 22, 2013 6:28:14 PM UTC+5:30, Paul Schreiner wrote: >>>Because no two people have the same file format or requirements, there >>> really >>>isn't a single solution to fit all needs. >>>> >>>>HOWEVER: >>>> >>>>IF all of the data sheets are in the same format and >>>>IF all of the data sheets have all entries >>>> >>>>Basically, if the value for "Cola" "Op STK" is always in cell C2 >>>>or even more basic: >>>> >>>>each of your 31 sheets is made from the same "template" >>>>so that all ITEMS are listed in the same row on all sheets.. >>>> >>>>Then, here's what I did in Excel 2010. >>>>(I THINK it works in
Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master sheet
I THINK I understand what you're trying to say...
You have a workbook with sheets named for each DAY of the month.
You want to SPECIFY a range to sum the values for those days
(from January 1, 2013 to January 5, 2013)
By using Excel functions (not VBA) you want formulas to update using the
specified date range.
I'll take a look and see if I can do it with an INDIRECT function.
I'll respond soon.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-
From: Rajesh thrissur
To: excel-macros@googlegroups.com
Sent: Thu, January 24, 2013 1:23:06 AM
Subject: Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master
sheet
Hi Paul
It can be identified by sheet name.I am trying to solve it by formula only.
Regards
Rajesh
On 1/22/13, Paul Schreiner wrote:
> how do you identify "date criteria"?
> is it by sheet name?
>
> I don't see the dates on any of the individual sheets.
>
> I'm pretty sure we can't include a date parameter with a simple function.
> We'd have to write some VBA to do it.
>
> Paul
> -
> “Do all the good you can,
> By all the means you can,
> In all the ways you can,
> In all the places you can,
> At all the times you can,
> To all the people you can,
> As long as ever you can.” - John Wesley
> -
>
>
>
>
> ________
> From: kasper
> To: excel-macros@googlegroups.com
> Sent: Tue, January 22, 2013 8:13:42 AM
> Subject: Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master
>
> sheet
>
> Hi Paul
>
> Thanks for the idea you gave. Is there any way to add date criteria also
> there
> eg: 01/01/13 to 05/01/13 or 01/01/13 to 10/01/13.
>
>
> With regards and thanks
>
> Rajesh kainikkara
>
> On Tuesday, January 22, 2013 6:28:14 PM UTC+5:30, Paul Schreiner wrote:
> Because no two people have the same file format or requirements, there
> really
> isn't a single solution to fit all needs.
>>
>>HOWEVER:
>>
>>IF all of the data sheets are in the same format and
>>IF all of the data sheets have all entries
>>
>>Basically, if the value for "Cola" "Op STK" is always in cell C2
>>or even more basic:
>>
>>each of your 31 sheets is made from the same "template"
>>so that all ITEMS are listed in the same row on all sheets..
>>
>>Then, here's what I did in Excel 2010.
>>(I THINK it works in earlier versions)
>>
>>First, make sure your "Consolidated" sheet is either the first or the last
>> sheet
>>of your workbook.
>>
>>In your "Consolidated" sheet, select cell C7 (Cola, Op Stk)
>>type =sum(
>>then select the first sheet (01)
>>hold down the shift key and select the last sheet (05 in the sample)
>>(Be sure to NOT include the Consolidated sheet)
>>
>>This will input:
>>=sum('01:05'!
>>in the cell
>>Your first sheet should be displayed (01)
>>click in the "Cola, Op STK" cell (C2)
>>
>>then type a closing parenthesis.
>>
>>You should end up with:
>>=SUM('01:05'!C2)
>>
>>This will sum all of contents of cell C2 in each of your sheets.
>>copy/drag this formula across the columns, then down the rows
>>and you should have your consolidation sheet.
>>
>>let me know how it works.
>>
>>
>>Paul
>>-- ---
>>“Do all the good you can,
>>By all the means you can,
>>In all the ways you can,
>>In all the places you can,
>>At all the times you can,
>>To all the people you can,
>>As long as ever you can.” - John Wesley
>>-- ---
>>
>>
>>
>>
>>
>
> From: Rajesh thrissur
>>To: excel-macros
>>Sent: Tue, January 22, 2013 7:36:40 AM
>>Subject: $$Excel-Macros$$ Consolidate data from 31 sheets to one master
>> sheet
>>
>>Hi Experts,
>>
>>Is there any method to consolidate data from many worksheets to one
>>master worksheet, here I attached a simple work book it have only 5
>>data sheets and 1 master sheet. being data is very less I solve it
>>with just adding data from reference cells but when data is more this
>>method is not practical. If there is any special
Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master sheet
I'm sorry, but I am unable to figure out a way to do what you ask.
I know if the date range is from January 1, 2013 to January 5, 2013
I want to end up with the formula:
=SUM('01:05'!C2)
I know I can use the formula:
TEXT(DAY(E3),"00")&":"&TEXT(DAY(G3),"00")
to get the string 01:05
but I cannot figure out how to make Excel "evaluate" the string.
I tried using =indirect()
to build the string, but couldn't get excel to do anything with it.
The best I could come up with was to use a formula like:
=IF(AND(1>=DAY($E$3),1<=DAY($G$3)),'01'!C2,0)
+IF(AND(2>=DAY($E$3),2<=DAY($G$3)),'02'!C2,0)
+IF(AND(3>=DAY($E$3),3<=DAY($G$3)),'03'!C2,0)
+IF(AND(4>=DAY($E$3),4<=DAY($G$3)),'04'!C2,0)
+IF(AND(5>=DAY($E$3),5<=DAY($G$3)),'05'!C2,0)
+IF(AND(6>=DAY($E$3),6<=DAY($G$3)),'06'!C2,0)
+IF(AND(7>=DAY($E$3),7<=DAY($G$3)),'07'!C2,0)
+IF(AND(8>=DAY($E$3),8<=DAY($G$3)),'08'!C2,0)
+IF(AND(9>=DAY($E$3),9<=DAY($G$3)),'09'!C2,0)
+IF(AND(10>=DAY($E$3),10<=DAY($G$3)),'10'!C2,0)
+IF(AND(11>=DAY($E$3),11<=DAY($G$3)),'11'!C2,0)
+IF(AND(12>=DAY($E$3),12<=DAY($G$3)),'12'!C2,0)
+IF(AND(13>=DAY($E$3),13<=DAY($G$3)),'13'!C2,0)
+IF(AND(14>=DAY($E$3),14<=DAY($G$3)),'14'!C2,0)
+IF(AND(15>=DAY($E$3),15<=DAY($G$3)),'15'!C2,0)
+IF(AND(16>=DAY($E$3),16<=DAY($G$3)),'16'!C2,0)
+IF(AND(17>=DAY($E$3),17<=DAY($G$3)),'17'!C2,0)
+IF(AND(18>=DAY($E$3),18<=DAY($G$3)),'18'!C2,0)
+IF(AND(19>=DAY($E$3),19<=DAY($G$3)),'19'!C2,0)
+IF(AND(20>=DAY($E$3),20<=DAY($G$3)),'20'!C2,0)
+IF(AND(21>=DAY($E$3),21<=DAY($G$3)),'21'!C2,0)
+IF(AND(22>=DAY($E$3),22<=DAY($G$3)),'22'!C2,0)
+IF(AND(23>=DAY($E$3),23<=DAY($G$3)),'23'!C2,0)
+IF(AND(24>=DAY($E$3),24<=DAY($G$3)),'24'!C2,0)
+IF(AND(25>=DAY($E$3),25<=DAY($G$3)),'25'!C2,0)
+IF(AND(26>=DAY($E$3),26<=DAY($G$3)),'26'!C2,0)
+IF(AND(27>=DAY($E$3),27<=DAY($G$3)),'27'!C2,0)
+IF(AND(28>=DAY($E$3),28<=DAY($G$3)),'28'!C2,0)
+IF(AND(29>=DAY($E$3),29<=DAY($G$3)),'29'!C2,0)
+IF(AND(30>=DAY($E$3),30<=DAY($G$3)),'30'!C2,0)
+IF(AND(31>=DAY($E$3),31<=DAY($G$3)),'31'!C2,0)
I put it in Cell C7 of your Consolidate sheet and copied it across and down.
It's not pretty, but it works.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-
From: Paul Schreiner
To: excel-macros@googlegroups.com
Sent: Thu, January 24, 2013 7:59:30 AM
Subject: Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master
sheet
I THINK I understand what you're trying to say...
You have a workbook with sheets named for each DAY of the month.
You want to SPECIFY a range to sum the values for those days
(from January 1, 2013 to January 5, 2013)
By using Excel functions (not VBA) you want formulas to update using the
specified date range.
I'll take a look and see if I can do it with an INDIRECT function.
I'll respond soon.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-
From: Rajesh thrissur
To: excel-macros@googlegroups.com
Sent: Thu, January 24, 2013 1:23:06 AM
Subject: Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master
sheet
Hi Paul
It can be identified by sheet name.I am trying to solve it by formula only.
Regards
Rajesh
On 1/22/13, Paul Schreiner wrote:
> how do you identify "date criteria"?
> is it by sheet name?
>
> I don't see the dates on any of the individual sheets.
>
> I'm pretty sure we can't include a date parameter with a simple function.
> We'd have to write some VBA to do it.
>
> Paul
> -
> “Do all the good you can,
> By all the means you can,
> In all the ways you can,
> In all the places you can,
> At all the times you can,
> To all the people you can,
> As long as ever you can.” - John Wesley
> -
>
>
>
>
>
> From: kasper
> To: excel-macros@googlegroups.com
> Sent: Tue, January 22, 2
Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master sheet
Hi
Thanks for the formula, it is right solution but when the sheet no is
more I have write a very long formula for this .Is it possible to cut
short the formula size using sumproduct & indirect function together
if any idea tell me.
With thanks & regards
Rajesh
On 1/24/13, Bé Trần Văn wrote:
> Attachment I use the formula in C7 for 5 days as follows:
>
> =SUMIF('01'!$B$2:$B$12;"COLA";'01'!C2:C12)+SUMIF('02'!$B$2:$B$12;"COLA";'02'!C2:C12)+SUMIF('03'!$B$2:$B$12;"COLA";'03'!C2:C12)+SUMIF('04'!$B$2:$B$12;"COLA";'04'!C2:C12)+SUMIF('05'!$B$2:$B$12;"COLA";'05'!C2:C12)
>
> Fill down and fill level you will get results
>
>
> If used for 31 days you just need to add and edit sheet name according
> to the number of days.
>
>
> 2013/1/24, Paul Schreiner :
>> I'm sorry, but I am unable to figure out a way to do what you ask.
>>
>> I know if the date range is from January 1, 2013 to January 5, 2013
>> I want to end up with the formula:
>> =SUM('01:05'!C2)
>>
>> I know I can use the formula:
>> TEXT(DAY(E3),"00")&":"&TEXT(DAY(G3),"00")
>>
>> to get the string 01:05
>>
>> but I cannot figure out how to make Excel "evaluate" the string.
>>
>> I tried using =indirect()
>> to build the string, but couldn't get excel to do anything with it.
>>
>> The best I could come up with was to use a formula like:
>> =IF(AND(1>=DAY($E$3),1<=DAY($G$3)),'01'!C2,0)
>> +IF(AND(2>=DAY($E$3),2<=DAY($G$3)),'02'!C2,0)
>> +IF(AND(3>=DAY($E$3),3<=DAY($G$3)),'03'!C2,0)
>> +IF(AND(4>=DAY($E$3),4<=DAY($G$3)),'04'!C2,0)
>> +IF(AND(5>=DAY($E$3),5<=DAY($G$3)),'05'!C2,0)
>> +IF(AND(6>=DAY($E$3),6<=DAY($G$3)),'06'!C2,0)
>> +IF(AND(7>=DAY($E$3),7<=DAY($G$3)),'07'!C2,0)
>> +IF(AND(8>=DAY($E$3),8<=DAY($G$3)),'08'!C2,0)
>> +IF(AND(9>=DAY($E$3),9<=DAY($G$3)),'09'!C2,0)
>> +IF(AND(10>=DAY($E$3),10<=DAY($G$3)),'10'!C2,0)
>> +IF(AND(11>=DAY($E$3),11<=DAY($G$3)),'11'!C2,0)
>> +IF(AND(12>=DAY($E$3),12<=DAY($G$3)),'12'!C2,0)
>> +IF(AND(13>=DAY($E$3),13<=DAY($G$3)),'13'!C2,0)
>> +IF(AND(14>=DAY($E$3),14<=DAY($G$3)),'14'!C2,0)
>> +IF(AND(15>=DAY($E$3),15<=DAY($G$3)),'15'!C2,0)
>> +IF(AND(16>=DAY($E$3),16<=DAY($G$3)),'16'!C2,0)
>> +IF(AND(17>=DAY($E$3),17<=DAY($G$3)),'17'!C2,0)
>> +IF(AND(18>=DAY($E$3),18<=DAY($G$3)),'18'!C2,0)
>> +IF(AND(19>=DAY($E$3),19<=DAY($G$3)),'19'!C2,0)
>> +IF(AND(20>=DAY($E$3),20<=DAY($G$3)),'20'!C2,0)
>> +IF(AND(21>=DAY($E$3),21<=DAY($G$3)),'21'!C2,0)
>> +IF(AND(22>=DAY($E$3),22<=DAY($G$3)),'22'!C2,0)
>> +IF(AND(23>=DAY($E$3),23<=DAY($G$3)),'23'!C2,0)
>> +IF(AND(24>=DAY($E$3),24<=DAY($G$3)),'24'!C2,0)
>> +IF(AND(25>=DAY($E$3),25<=DAY($G$3)),'25'!C2,0)
>> +IF(AND(26>=DAY($E$3),26<=DAY($G$3)),'26'!C2,0)
>> +IF(AND(27>=DAY($E$3),27<=DAY($G$3)),'27'!C2,0)
>> +IF(AND(28>=DAY($E$3),28<=DAY($G$3)),'28'!C2,0)
>> +IF(AND(29>=DAY($E$3),29<=DAY($G$3)),'29'!C2,0)
>> +IF(AND(30>=DAY($E$3),30<=DAY($G$3)),'30'!C2,0)
>> +IF(AND(31>=DAY($E$3),31<=DAY($G$3)),'31'!C2,0)
>>
>> I put it in Cell C7 of your Consolidate sheet and copied it across and
>> down.
>>
>> It's not pretty, but it works.
>>
>> Paul
>> -
>> “Do all the good you can,
>> By all the means you can,
>> In all the ways you can,
>> In all the places you can,
>> At all the times you can,
>> To all the people you can,
>> As long as ever you can.” - John Wesley
>> -
>>
>>
>>
>>
>> ____________
>> From: Paul Schreiner
>> To: excel-macros@googlegroups.com
>> Sent: Thu, January 24, 2013 7:59:30 AM
>> Subject: Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one
>> master
>>
>> sheet
>>
>>
>> I THINK I understand what you're trying to say...
>>
>> You have a workbook with sheets named for each DAY of the month.
>> You want to SPECIFY a range to sum the value
Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master sheet
I stipulated that you wanted this done without macros.
I do not think it is possible to make this "flexible"
so that you can change the date range and have it automatically update
by using sumproduct & indirect.
I included my recommendation (using multiple "IF")
>> =IF(AND(1>=DAY($E$3),1<=DAY($G$3)),'01'!C2,0)
>> +IF(AND(2>=DAY($E$3),2<=DAY($G$3)),'02'!C2,0)
>> +IF(AND(3>=DAY($E$3),3<=DAY($G$3)),'03'!C2,0)
you would only have to write the formula once and copy it throughout your
summary sheet.
No further updates would be required (unless you change columns in the data
sheets)
Now, if you would be willing to change the data sheets so that the day #
is in each ROW, then it might be possible.
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-
From: Rajesh thrissur
To: excel-macros@googlegroups.com
Sent: Fri, January 25, 2013 1:22:48 AM
Subject: Re: $$Excel-Macros$$ Consolidate data from 31 sheets to one master
sheet
Hi
Thanks for the formula, it is right solution but when the sheet no is
more I have write a very long formula for this .Is it possible to cut
short the formula size using sumproduct & indirect function together
if any idea tell me.
With thanks & regards
Rajesh
On 1/24/13, Bé Trần Văn wrote:
> Attachment I use the formula in C7 for 5 days as follows:
>
>=SUMIF('01'!$B$2:$B$12;"COLA";'01'!C2:C12)+SUMIF('02'!$B$2:$B$12;"COLA";'02'!C2:C12)+SUMIF('03'!$B$2:$B$12;"COLA";'03'!C2:C12)+SUMIF('04'!$B$2:$B$12;"COLA";'04'!C2:C12)+SUMIF('05'!$B$2:$B$12;"COLA";'05'!C2:C12)
>)
>
> Fill down and fill level you will get results
>
>
> If used for 31 days you just need to add and edit sheet name according
> to the number of days.
>
>
> 2013/1/24, Paul Schreiner :
>> I'm sorry, but I am unable to figure out a way to do what you ask.
>>
>> I know if the date range is from January 1, 2013 to January 5, 2013
>> I want to end up with the formula:
>> =SUM('01:05'!C2)
>>
>> I know I can use the formula:
>> TEXT(DAY(E3),"00")&":"&TEXT(DAY(G3),"00")
>>
>> to get the string 01:05
>>
>> but I cannot figure out how to make Excel "evaluate" the string.
>>
>> I tried using =indirect()
>> to build the string, but couldn't get excel to do anything with it.
>>
>> The best I could come up with was to use a formula like:
>> =IF(AND(1>=DAY($E$3),1<=DAY($G$3)),'01'!C2,0)
>> +IF(AND(2>=DAY($E$3),2<=DAY($G$3)),'02'!C2,0)
>> +IF(AND(3>=DAY($E$3),3<=DAY($G$3)),'03'!C2,0)
>> +IF(AND(4>=DAY($E$3),4<=DAY($G$3)),'04'!C2,0)
>> +IF(AND(5>=DAY($E$3),5<=DAY($G$3)),'05'!C2,0)
>> +IF(AND(6>=DAY($E$3),6<=DAY($G$3)),'06'!C2,0)
>> +IF(AND(7>=DAY($E$3),7<=DAY($G$3)),'07'!C2,0)
>> +IF(AND(8>=DAY($E$3),8<=DAY($G$3)),'08'!C2,0)
>> +IF(AND(9>=DAY($E$3),9<=DAY($G$3)),'09'!C2,0)
>> +IF(AND(10>=DAY($E$3),10<=DAY($G$3)),'10'!C2,0)
>> +IF(AND(11>=DAY($E$3),11<=DAY($G$3)),'11'!C2,0)
>> +IF(AND(12>=DAY($E$3),12<=DAY($G$3)),'12'!C2,0)
>> +IF(AND(13>=DAY($E$3),13<=DAY($G$3)),'13'!C2,0)
>> +IF(AND(14>=DAY($E$3),14<=DAY($G$3)),'14'!C2,0)
>> +IF(AND(15>=DAY($E$3),15<=DAY($G$3)),'15'!C2,0)
>> +IF(AND(16>=DAY($E$3),16<=DAY($G$3)),'16'!C2,0)
>> +IF(AND(17>=DAY($E$3),17<=DAY($G$3)),'17'!C2,0)
>> +IF(AND(18>=DAY($E$3),18<=DAY($G$3)),'18'!C2,0)
>> +IF(AND(19>=DAY($E$3),19<=DAY($G$3)),'19'!C2,0)
>> +IF(AND(20>=DAY($E$3),20<=DAY($G$3)),'20'!C2,0)
>> +IF(AND(21>=DAY($E$3),21<=DAY($G$3)),'21'!C2,0)
>> +IF(AND(22>=DAY($E$3),22<=DAY($G$3)),'22'!C2,0)
>> +IF(AND(23>=DAY($E$3),23<=DAY($G$3)),'23'!C2,0)
>> +IF(AND(24>=DAY($E$3),24<=DAY($G$3)),'24'!C2,0)
>> +IF(AND(25>=DAY($E$3),25<=DAY($G$3)),'25'!C2,0)
>> +IF(AND(26>=DAY($E$3),26<=DAY($G$3)),'26'!C2,0)
>> +IF(AND(27>=DAY($E$3),27<=DAY($G$3)),'27'!C2,0)
>> +IF(AND(28>=DAY($E$3),28<=DAY($G$3)),'28&#
