telekinetic redhead chick (openended) wrote in excel_juice,
telekinetic redhead chick

=SUM...without hidden values.

=SUM((C12*D$7)+(G12*H$7)+(K12*L$7)+(O12*P$7)+(S12*T$7)) is my current formula. However, there's the chance that at least one of the columns C, G, K, O or S might be hidden. Without first making sure that there's absolutely no data in any of those cells (because this is being coded for widespread use throughout my company and not everyone is up to speed on sensibilities), how can I change that formula so that it will NOT include any hidden values?

I'm using Excel 2002 (or 2003, I honestly can't remember what we have at work).

Hilariously, there's a Microsoft support site that attempts to answer this question but not only do they admit that there's no easy way to do it, their formula doesn't work for me.
  • Post a new comment


    Anonymous comments are disabled in this journal

    default userpic

    Your IP address will be recorded 

  • 1 comment
There's no formulaic way to read the width of a column. I believe it can be done in VB, but I've never tried. If you can find the VB script anywhere that reads a cell's width, then you could pretty easily write one to crawl across the top recording widths and making note of which are hidden. It could leave a marker so that the formula knows not to add the hidden ones.

Unfortunately, using the hide feature is not a good way of indicating to excel that the cells are not meant to be used.

Having said that, were I faced with that dilemma I would cheat.

When you say it is meant to be used company wide, you don't just mean the formula, you mean the spreadsheet, right?

Well, if that's the case, then don't let people just hide the columns. Give 'em an alternative.

Some suggestions:
1. A button at the top of each of the named columns that, when pressed checks the top cell of the column for a marker you name (ie: TRUE or FALSE), if TRUE, then it changes it to false, and hides the column. If FALSE, it does the reverse. Then, in your formula, I would include an IF statement that does not SUM the column if $O$1=FALSE. The kicker is they'd all have to use the button, and not the hide command. You could, if you so desire, have it not hide the column, but turn the text white (or gray or whatever) so it is invisible, but not hidden so they don't think to just hide it like they used to.)

2. A checkbox at the top of each column (or, really, anywhere) that indicates which ones to SUM and which to overlook. Pretty much same as above beyond that. It would be their responsibility to uncheck the ones they've hidden.

3. (not a very satisfactory answer) use something other than 'hide' to void a column. It would be MUCH easier to have an indicator telling you if those columns are populated when they should not be. (just a new column which says "CGKOS" or "12345" (or some combination thereof) when there is data in 'em.

Let me know what avenue you're interested in and I might be able to help. Sorry I couldn't get you what you'd requested specifically (unless you locate that VB command)