Function_num

[Moti]

Can anyone help me with this function?:

its part of the subtotal syntax for including or excluding hidden rows:
=SUBTOTAL(Function_num,[ref1],[ref2]) (9)

Is there a way to get this function to exclude hidden columnn as well.
currently it only works to exclude hidden (grouped) rows.

Thanks.

 

[Fazza]

What about a little cheating? Work with existing Excel functionality (rows) but align all your font at ±90° !! :wink:

Fazza

 

[Fazza]

And, turn the monitor on its side to improve workability

 

[Moti]

Brilliant!
Made me laugh but somehow I dont think it's gonna work.

 

[Fazza]

One of my more imaginitive cheats!

 

[Mark W.]

Why don't you organize your data in 1st Normal Form and bypass the problem altogether?

 

[Moti]

sounds good to me.
Can you explain more?

 

[Mark W.]

http://databases.about.com/od/specificproducts/l/aa1nf.htm

 

[Moti]

Thanks again. I am familiar with the concept of normalized data, I just dont know how it can solve my problem.

 

[Mark W.]

Thanks again. I am familiar with the concept of normalized data, I just dont know how it can solve my problem.

Well, for one you wouldn't be looking for a way to sum across columns... you'd be using a PivotTable to summarize your data.

Storing data in Excel as a list is fairly fundamental -- there's even a help topic describing lists. A properly designed list increases the utility that you can obtain from Excel's built-in features.

 

[Moti]

No, pivot tables wouldnt work in this case.
the workbook is curently made up of 9 worksheets that all get pulled to a main sheet using vlookups.

Its the most efficient way to organize the data that I can think of. Pivoting the data would not be easier at all.

The sheet I described in my examples above was only an example. Actually the periods are weeks not months and each week has 9 seperate headings, some of which are averaged some of which are claculated normally. Every update requires new formulea and calulations.