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

sum variable range of columns

C

ClairePS

New Member
I have a workbook that summarises the data from several pivot tables onto a tab each for, plan, forecast, actual and each of these will be month only and also ytd. Apart from the tabs with the pivot tables on there others including one that picks up the month only actuals and the year to date actuals.

I am trying to find a formula that will pick up the correct columns each month.
I use the whole column because the number of records can vary.
I did think that a sumif combined with offset would work, but I am going round in circles.

In the file already I have used INDIRECT, INDEX,MATCH and others so I am happy to learn something new.

Thankyou for your attention

Claire
 

Attachments

Hi Claire ,

Rather than discuss your formula , can you describe what you want done ?

What are the data , what are the fields which should be matched between the two tabs , and what should be the output ?

Narayan
 
Hi Narayan,

of course - I was not being logical.

on the "FTE_trend_exc_NPR" tab FTE is recorded by month and by department (Cost Centre in column H). The data is arranged into sets of Actual, Plan, Q1F (and more might be added over the course of the year). What I need to do on the "FTE info" tab is to summarise the FTE by department (in column H) both month only and year to date.

In the finished work book each of the columns on the "FTE info" tab will actually be on different tabs.

In theory, each Cost centre in column H should appear only once in that column.
The cost centres are not necessarily in the same order on the "FTE_trend_exc_NPR" tab and the "FTE info" tab

the data in the colored cells E1:G3 are populated from a control sheet.

Have I covered everything?

thankyou

Claire
 
Hi:

Please find the attached. I am not sure this is what you are looking for.I have given formulas based on month drop downs (Yellow cell). The monthly data will pull the numbers for the month you are selecting from the drop downs and the YTD data will give the sum of numbers from Jan till the month you are selecting in the drop down. Let me know with questions if any.

Thanks
 

Attachments

Thankyou very much Nebu.

the formula certainly works and does achieve what I want.

I think that I have made things tricky by trying to use entire columns (e.g. $H:$H) rather than estimating the length of the range that will be required.

Thankyou again both Nebu and Narayan for you help

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