Making YTD worksheet from each Weekly tab

[umair434]

Hi all,

I have an excel file with multiple tabs. Each tab represents data for a week i.e total number of product cancellations in a week because of a particular reason.

I want to create a YTD worksheet which would show me total cancellations of a product/reason..


the problem is that:

Every week the "reason/cause of cancellation" changes its order because the reason with the highest number of cancellations needs to be at the top. This means that I cannot copy each weekly sheet into YTD worksheet


I know it's confusing. Anyone willing tohelp

thanks!!

 

[NBVC]

If you can assign consistent cells in each worksheet with a tally of the respective sheet, then in the summary sheet you can use a formula like:

=SUM('Sheet1:Sheet10'!X1)

this would sum all the X1 cells from Sheet1 to Sheet10.

If you will continue to insert sheets, then you can add some so-called book-end sheets.

Insert a sheet before all the weekly sheets and one after the sheets, call them "Start" and "End", respectively (without the quotes)... then use formula

=SUM('Start:End'!X1)

Now you can insert sheets between Start and End and the value of X1 will automatically be included in your summary sheet.

 

[umair434]

thanks NBVC!

I actually used vertical look up function and made a new worksheet which had all the weekly data - Now I can manually sum up all the rows n columns to find YTD values - but how do I automate the process? I mean is there any function/macro/formula where YTD gets updated everytime a new column is entered.

right now i have 29 columns - one for YTD and the rest for 28 weeks. An Ideal situation would be that the YTD column gets updated as new weeks are added to the worksheet.

Thanks again for taking out your time to help me. Much appreciated!

 

[NBVC]

So, if I understand you correctly.... you have say A2:AB2 with weekly numbers and AC2 has the YTD total sum. Now you want to insert a column between AB2 and AC2 and have the new YTD total account for that inserted column. Is that correct? If so try something like:

=SUM(A2:INDEX(A2:AC2,COLUMN(AC2)-1))

Where AC2 is the YTD cell in row 2. You can copy the formula down.

 

[umair434]

thanks NBVC!!

that's exactly what i was looking for. But I have 1 question just out of curiosity:

My first column is the name of the rootcause for these cancellations, so I started the formula from B1, and it doesn't give me the right answer.

but when I start from A1 - it gives me the correct sum even though A1 is pure text. I'm just wondering what could be the issue here.

Nevertheless, I got it working and thanks for helping. This forum and you guys are great

 

[NBVC]

If you are going to start in B1, you need to adjust the COLUMN(AC2) part because then it tries to include AC2 in the calculation giving a circular reference...

Try:

=SUM(B2:INDEX(B2:AC2,COLUMNS(B2:AC2)-1))

 

[umair434]

i tried the new formula, but that just returns the values of column B in the YTD column :S

thanks!

 

[umair434]

oh shooott!! it;s "COLUMNS'' this time instead of "COLUMN"

it works now. great! thanks