Hidden sheets and cumulative totals

scouse

Board Regular
Joined
Jan 16, 2004
Messages
207
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,518
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

Board Regular
Joined
Jan 16, 2004
Messages
207
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

Well-known Member
Joined
Jul 18, 2003
Messages
4,518
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

Board Regular
Joined
Jan 16, 2004
Messages
207

ADVERTISEMENT

Thats it exactly Alan,

and sadly they are not adjacent (boo!)

but thanks again for your help

cheers

scouse
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,518
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

Board Regular
Joined
Jan 16, 2004
Messages
207
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

Well-known Member
Joined
Jul 18, 2003
Messages
4,518
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,871
Messages
5,766,857
Members
425,382
Latest member
IronM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top