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 <betnmtdongna...@gmail.com> 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 <schreiner_p...@att.net>:
>> 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 <schreiner_p...@att.net>
>> 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 <rajeshkainikk...@gmail.com>
>> 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 <schreiner_p...@att.net> 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 <rajeshkainikk...@gmail.com>
>>> 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 <rajeshka...@gmail.com>
>>>>To: excel-macros <excel-...@googlegroups.com >
>>>>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.
>>>
>>> --
>>> 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.
>>
>>
>> --
>> 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.
>>
>>
>>
>
> --
> 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 unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
