Hidden sheets and cumulative totals

[scouse]

Greetings,

still having trouble with cumulative totals despite all the great advice i have received on this board.

I have a formula sum(a1, e1, I1.......bb1) which adds up to a cumulative total for the year. However whilst I dont mind ppl entering figures for I1 (which represents march) i dont actually want it to add into the formula automatically.

I know i may be asking the impossible.......

Is there any way by using SumProduct, or conditional formatting or hiding the relevant cells that i can acheive

I cant really afford to go back and change all the formulas too much as, presently i have over 6000 which are affected by this.

once again thanks for all your advice, this is an excellent message board

scouse

 

[al_b_cnu]

Hi,

not too sure what your criteria are for NOT including a figure in the sum, but how about:

a) change the s/sheet to Manual Calculation
or
b) copy / past special / values the sum formulas

HTH

Alan

 

[scouse]

Have to take figures out because they are only monthly targets, which, if included would be released as the figure for the year so far!!

e.g. Jan Target 20, Feb Target 20 - sum 40
Cumulatively 40 however, as it is only January it should be only 20 at the moment

Not sure what the copy/paste special does or the manual calculations but will have a go


Thanks for your help

 

[al_b_cnu]

So what you're saying is that you want to only sum a certain number of entries, depending on the current month, i.e.

for Jan, SUM(A1)

For Feb, SUM(A1,E1)

etc.

If the columns were adjacent, easy peasy, but with them not being, not so sure.

Alan

 

[scouse]

Thats it exactly Alan,

and sadly they are not adjacent (boo!)

but thanks again for your help

cheers

scouse

 

[al_b_cnu]

Hi,

Assuming your data sheet is called 'Data', how about ...

1) create a sheet name 'Work'

2) In A1 put the formula =INDIRECT("Data!"&ADDRESS(ROW(),((COLUMN()-1)*5)+1))

3) in B1 put the formula =A1+INDIRECT("Data!"&ADDRESS(ROW(),((COLUMN()-1)*5)+1))

4) Copy the formula in B1 across to L1

5) copy row 1 down for the number of rows in your 'Data' sheet

6) In your Data sheet, assuming that you want your sum to be in column BI, type the formula =INDIRECT("Work!"&ADDRESS(ROW(),MONTH(NOW()))) & copy down.


HTH

Alan

 

[scouse]

Sorry Alan,

not working (i think to be more exact I cant get it to work!)

Martin the IF statement for Jan is working. I click on the drop down menu and it only counts the figures from Jan into the Cumulative Total - thats exactly what i want. All i need now is to nest IF statements so that if i choose Feb it will sum these figures up but exclude March - Dec.

As i said earlier i need to nest 12 IF statements.

Its either that or create 12 Validation Lists and choose the valid month.

Thanks for all your help lads - its been excellent.

*******************
To play devils advocate if the spreadsheet had been set up in such a way that the sum could be done in a col (at the moment they are done in rows e.g. all Targets So Far this Year are contained in BB1, BB2.......BB980) would it be easier to manage?

 

[al_b_cnu]

You're right! Its not working!

If the data sheet contains 4 columns per month starting at column A, try the following:

1) In the 'Work' sheet,
Cell A1 = '=INDIRECT("Data!"&ADDRESS(ROW(),((COLUMN()-1)*4)+1))'
Cell B1 = '=A1+INDIRECT("Data!"&ADDRESS(ROW(),((COLUMN()-1)*4)+1))'
Copy cell B1 across to C1:L1
Copy A1:L1 down for each row in the data sheet

2) set up a named range 'CurMonth' & place the current month in it, or set it to =Month(now())

3) In the Data Sheet at the end of the data columns, presumably column AW, place the formula '=INDIRECT("Work!"&ADDRESS(ROW(),CurMonth))' & copy it down for each row. This is your SUM formula.

HTH

Alan