Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Consolidate report in Different sheet
Hi,
I have a sheet where I collect data on a daily basis. I want to consolidate the same data in a separate sheet date wise. The details pertaining to that date should be displayed in the new sheet. Have a look at the sample and reply. I want use filters. Thanks a lot in advance. Data collection sheet Date Mode Route Prodcuct1 Product2 Product3 Total cash Total 1-2-09 Sales Line 1 10 10 10 Rs.3000 30 1-2-09 Sales line2 5 20 Rs.4000 15 1-2-09 return line1 5 10 -Rs.3000 -15 2-2-09 Sales Line3 10 10 10 Rs.5000 30 2-2-09 Sales Line4 50 Rs.6000 50 Consolidation sheet Filter all columns Date mode route product1 product2 product3 Total cash total products 1-2-09 In/out line1 10 30 0 Rs.3000 40 2-2-09 In/Out Line3 60 10 Rs.6000 70 All prices are just examples... kindly use filters and help me Thanks KK |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Consolidate report in Different sheet
Hi
You will need to enter - values in the the return items. Then DataSubtotalAt each change inDateUse functionSumTick all of the boxesOK Now if you choose View 2 from the 1,2,3 that appears below the Name box, then you will get the report you want. Note the result is different from what you show Day 1 cash is 4000 Day 2 cash is 11000 -- Regards Roger Govier "Krishna Kumar L" wrote in message ... Hi, I have a sheet where I collect data on a daily basis. I want to consolidate the same data in a separate sheet date wise. The details pertaining to that date should be displayed in the new sheet. Have a look at the sample and reply. I want use filters. Thanks a lot in advance. Data collection sheet Date Mode Route Prodcuct1 Product2 Product3 Total cash Total 1-2-09 Sales Line 1 10 10 10 Rs.3000 30 1-2-09 Sales line2 5 20 Rs.4000 15 1-2-09 return line1 5 10 -Rs.3000 -15 2-2-09 Sales Line3 10 10 10 Rs.5000 30 2-2-09 Sales Line4 50 Rs.6000 50 Consolidation sheet Filter all columns Date mode route product1 product2 product3 Total cash total products 1-2-09 In/out line1 10 30 0 Rs.3000 40 2-2-09 In/Out Line3 60 10 Rs.6000 70 All prices are just examples... kindly use filters and help me Thanks KK |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Consolidate report in Different sheet
Hi,
I am unable to understand your solution. I need all details to that date to reflect in the new sheet. This way I can find out all the products I sold on that day and also the route/line I sold the same. Another thing I will get is the amount of outward and Inward. Outward and Inward is sales and sales return. Every month I will be using a new sheet. Thank you KK "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi You will need to enter - values in the the return items. Then DataSubtotalAt each change inDateUse functionSumTick all of the boxesOK Now if you choose View 2 from the 1,2,3 that appears below the Name box, then you will get the report you want. Note the result is different from what you show Day 1 cash is 4000 Day 2 cash is 11000 -- Regards Roger Govier "Krishna Kumar L" wrote in message ... Hi, I have a sheet where I collect data on a daily basis. I want to consolidate the same data in a separate sheet date wise. The details pertaining to that date should be displayed in the new sheet. Have a look at the sample and reply. I want use filters. Thanks a lot in advance. Data collection sheet Date Mode Route Prodcuct1 Product2 Product3 Total cash Total 1-2-09 Sales Line 1 10 10 10 Rs.3000 30 1-2-09 Sales line2 5 20 Rs.4000 15 1-2-09 return line1 5 10 -Rs.3000 -15 2-2-09 Sales Line3 10 10 10 Rs.5000 30 2-2-09 Sales Line4 50 Rs.6000 50 Consolidation sheet Filter all columns Date mode route product1 product2 product3 Total cash total products 1-2-09 In/out line1 10 30 0 Rs.3000 40 2-2-09 In/Out Line3 60 10 Rs.6000 70 All prices are just examples... kindly use filters and help me Thanks KK |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Consolidate report in Different sheet
Hi
My suggestion does not put the data on another sheet. It summarises the data by day, giving the Totals that you want for that day, and "hiding" all the detail lines. Clicking the "+" button next to any day, will expose all of the lines making up that Sub total, and the icon will change to a "-". Clicking the "-" will hide all the detail again and just show you a single line total for the day. As I said in my first posting, you will have to ensure that all entries on the Return rows are entered as Negative values. I will email a small sample file to you. -- Regards Roger Govier "Krishna Kumar L" wrote in message ... Hi, I am unable to understand your solution. I need all details to that date to reflect in the new sheet. This way I can find out all the products I sold on that day and also the route/line I sold the same. Another thing I will get is the amount of outward and Inward. Outward and Inward is sales and sales return. Every month I will be using a new sheet. Thank you KK "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi You will need to enter - values in the the return items. Then DataSubtotalAt each change inDateUse functionSumTick all of the boxesOK Now if you choose View 2 from the 1,2,3 that appears below the Name box, then you will get the report you want. Note the result is different from what you show Day 1 cash is 4000 Day 2 cash is 11000 -- Regards Roger Govier "Krishna Kumar L" wrote in message ... Hi, I have a sheet where I collect data on a daily basis. I want to consolidate the same data in a separate sheet date wise. The details pertaining to that date should be displayed in the new sheet. Have a look at the sample and reply. I want use filters. Thanks a lot in advance. Data collection sheet Date Mode Route Prodcuct1 Product2 Product3 Total cash Total 1-2-09 Sales Line 1 10 10 10 Rs.3000 30 1-2-09 Sales line2 5 20 Rs.4000 15 1-2-09 return line1 5 10 -Rs.3000 -15 2-2-09 Sales Line3 10 10 10 Rs.5000 30 2-2-09 Sales Line4 50 Rs.6000 50 Consolidation sheet Filter all columns Date mode route product1 product2 product3 Total cash total products 1-2-09 In/out line1 10 30 0 Rs.3000 40 2-2-09 In/Out Line3 60 10 Rs.6000 70 All prices are just examples... kindly use filters and help me Thanks KK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consolidate an XLS sheet | Excel Discussion (Misc queries) | |||
trying to consolidate info into one sheet | Excel Worksheet Functions | |||
Consolidate data to summary sheet | Excel Discussion (Misc queries) | |||
Consolidate multiple worksheets into new sheet | Excel Worksheet Functions | |||
How do I consolidate 2 tables into 1 pivot report? | Excel Discussion (Misc queries) |