• 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.

Creating link between different worksheets

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 10, Sheet 2 Value 20, Sheet 3 50



I need Solution Like these

Sheet1 Value should show as 10, Sheet 2 value should show as 20-10=10 and sheet 3 Value should as =50-(sheet1+sheet2).
The total value in Summary sheet should show as 50 (Sheet1+Sheet2+Sheet3).
Please help on urgent basis. It will be of great help
 

Attachments

Hi Arup ,

Can you confirm the following ?

1. Where will the data entry for each day be done ? Will it be done in the tabs named 1 , 2 , 3 ,... ?

2. How many such tabs will there be ?

3. How many rows / columns of data will there be on each day ?

4. If you are going to enter 20 on Sheet2 for day 2 , how can the 20 become 10 ? Do you mean to say that the data entry will be in one area of the worksheet , and another area should now display the day's value ?

Can you not change the layout so that all of the data entry is in one tab ? Put the days from 1 to 31 as the column headers , and your present headers as the row headers ; put the values in this matrix.

Since this tab will always have all the data for all the days , splitting this up into individual day's values in separate tabs will be easier.

5. Is VBA acceptable ?

6. Can you confirm that the sample workbook you have uploaded is representative of your working file ? It will be a waste of time if a solution is suggested for the sample file , and then it is found that the sample file was not representative of your working file.

Narayan
 
Dear Narayan,
The tab will be for a month . Each Day will represent it's single value. See I will be downloading reports from a file and have present a sales report which will show how many sales figure have been achieved by the salesman for that particular day.

For example when I'm downloading the report and putting it in my excel file for Day 1 it will be not a problem because Day 1 has got only single day report .
2) In Day 2 when I'm downloading the report it is a combination of Day1 and Day2 sales report . In my report I want only day 2 achievement(FTD -For the Day) and in the the summary sheet I will show Day1+ Day2 combined achievement means MTD(Month till date ) .Like this.
3) I want it like these when putting the raw data which i received from ERP system and putting it in my excel file . It will automatically show only that particulars achievement.Means if I want to extract Day 2 achievement only then manually I don't want to do subtraction. Like I don't want subtract Day 2 figure from Day 1 Figure because Day 2 report is combination of Day1+Day2 report (MTD report)to extract only Day 2 achievement.
4) VBA is acceptable. I would appreciate if you help me without using VBA.
Please help on urgent basis.
 
Hi Arup ,

I am still not clear about a few points :

1. You say that you are copy + pasting cumulative figures each day ; isn't this what you are asking for in your Summary tab ?

2. Why can you not do a copy + paste each day in your Summary tab , so that the Summary tab will have the figures for the entire month. From this Summary tab , we can derive the individual day's figures.

3. Why do you want the individual day's figures in individual tabs ? Why not have a single tab , which will have the days as the column headers , and since this layout will be identical to the layout in the Summary tab , the formulae for arriving at the individual day's figures will be very simple.

Narayan
 
Dear NarayanK991,
Since it is a huge file. In Summary sheet it will not be possible to have the whole months figure in a single tab. Because in my file there will be minimum 350 rows and 1000 columns. So you can understand how big summary sheet it will be if I put whole months data in day wise basis in a single summary sheet . This is the reason I want to create day wise tab along with the summary sheet.
Please help on urgent basis.
 
Hi Arup ,

Repeating something does not necessarily pay dividends ; if your problem was urgent , it would have helped if the initial post had been clear and comprehensive , so that whoever wished to answer would not have had any doubts ; 8 hours have been wasted in clarifications.

Your sample file had one row and 4 columns of data ; now you are mentioning 350 rows and 1000 columns !

I am sorry but I cannot help unless you upload a proper file , with realistic enough data , and with the proper layout.

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