• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Summary for Excel file

A

Arup Dutta Chowdhury

Member
I have got an excel file. There are several sheets in that excel file. Every day I have to put the sales report in that excel file As Day 1, Day2, Day 3. I want that if I put Day1 data then in the summary sheet it will show as Day1 and when I put Day 2 in the summary sheet the report will come as Day1+Day2 but in the Day 2 sheet the report will come as day2-day1 means on day2 achievement. Likewise when I put day 3 value the summary sheet will show Day1+Day2+Day3 value but in Day3 sheet it will come as Day3-(Day+Day2).

I have given example how it will look like :

Problem

Sheet1 Value is of Day1, Sheet 2 comprises of Day1+Day2, Sheet 3 Comprises of Day1+Day2+Day3. I have taken the data from the Erp system in excel format.



I need Solution Like these

Sheet1 Value should show as Day1 Value, Sheet 2 value should show as Day2-Day1 Value10 and Sheet 3 Value should show as : Day3-(Day1+Day2).

I want that when I put the Day 1 figure in my excel file from the erp system Excel will automatically take it. Then when I put the Day 2 Value which consists of Day1+Day2 value from Erp Excel automatically substract the value of Day2 (FTD figure – For the Day )from Day1 and put it in Day2 worksheet. I don’t have to manually subtract the Day2 figure from Day1.

In the summary Sheet total value should show means Day1+Day2 Value. Means MTD Figure (MTD -Month till Date).

Since the file is huge I haven't included the Day 3 figure.

Please help me.
 

Attachments

  • Sample.xlsx
    608.8 KB · Views: 7
Hi Arup ,

See if this works ; I have put in the formulae only for the first 2 rows.

I have defined a named range called LastWorksheet , which will be updated when ever you open the workbook.

The tab named temp can be renamed to 3 , in which case the figures on the Summary tab should get updated , provided you run the Workbook_Open macro , or you save and reopen the workbook.

Narayan
 

Attachments

  • Sample (2).xlsm
    822.2 KB · Views: 1
Hi Narayank,
I want that when I put the Day 2 data it will automatically subtract the figure of Day 1 from it because the Day 2 raw data from Erp system which I will dowload and paste it in the sample file will consist of Day1+Day2 figure. Now is it possible that excel will automatically subtract the Day2 figure from Day1 figure and show only Day2 figure in Day2 tab . I don't want to manually subtract Day2 figure from Day1 figure.
 
Last edited:
Hi Arup ,

I have asked you a specific question ; instead of answering that question , you are asking me one !

What you are asking for is impossible ; the output should never be in the same area as the input , since if there is any problem , your input data will get mixed up with your output data.

You need to use another 31 tabs to keep track of the individual day's data , or use the same tabs but put the individual day's data in a separate area , away from the area where you will paste the input data.

Narayan
 
You must log in or register to reply here.
Back
Top