0

I basically want to ignore random columns in excel. Is there a way to detect if a column is hidden, and then not include that column in the formula?

And example would be F1 = B1 + C1 + E1(ignoring column D) but the next day Column F may need to = B + D + E instead.

Is there a way to simply achieve this? I've seen some formulas that ignore specific columns, but nothing dynamic that can detect the hidden data and then not include it. Thanks!!

Example

enter image description here

So basically, If I hide Greg's column, I want the total for all the rows to reflect that change. So E2 would then equal 8 instead of 12 when his column is hidden.

Formula not working correctly

The second image here shows the formula not working as expected

bkoverflow
  • 1
  • 1
  • 3
  • 3
    If you aren't tied to the structure of your data, the `SUBTOTAL` function gives the ability to exclude hidden rows (but unfortunately it doesn't work for columns) – guitarthrower Feb 18 '15 at 18:27
  • hello, i would like to let you know that you are missing `IF` functions in the formula (shown in the OP image) which mentioned in the below answer by @WaiHaLee . And you need to recalculate the formulas everytime you hide a column. you can use `F9` for that. I think WaiHaLee's answer is brilliant and working fine for me. – Dubison Feb 19 '15 at 16:10

4 Answers4

4

Method 1: using CELL and INDIRECT.

Cell function

The cell function allows you find properties of a cell, e.g. the width:

=CELL("width")

Indirect function

The Indirect function allows you to refer to cells via a string, e.g. to get the value of A1,

=INDIRECT("A1")

or

=INDIRECT("R1C1", true)

Take the sum of everything (e.g. E2 = B2 + C2 + D2), then use CELL to find out if each cell width is zero, then take it away from the sum.

Method 2: using IF and CELL

Or, you can use the IF function and the CELL function, e.g.

E1 = IF(CELL("width", B2) = 0, 0, B2)
   + IF(CELL("width", C2) = 0, 0, C2)
   + IF(CELL("width", D2) = 0, 0, D2)
Wai Ha Lee
  • 8,598
  • 83
  • 57
  • 92
  • 1
    Thanks! I added an Example pic link to my original post, with a paragraph below it showing what I am looking for. – bkoverflow Feb 18 '15 at 19:19
  • Hi @bkoverflow, I've updated my answer to include a pastable answer that would work for your example. – Wai Ha Lee Feb 18 '15 at 20:03
  • Great solution, +1. You could shorten Method 2 as `=(CELL("width",B2)>0)*B2 + (CELL("width",C2)>0)*C2 + (CELL("width",D2)>0)*D2`, but at the risk of losing legibility. – Rick Hitchcock Feb 18 '15 at 21:13
  • @RickHitchcock, many thanks for your feedback, and for the +1. I don't think it hinders legibility, but I might leave my answer as-is to leave it explicit. – Wai Ha Lee Feb 18 '15 at 21:28
  • Thanks for all your help guys!!! Unfortunately neither function is properly ignoring the column. I added another screenshot to my original post showing Method 2 with Column D hidden. As you can see, the total in Column E is still adding the value in Column D to the formula. – bkoverflow Feb 18 '15 at 22:09
  • @bkoverflow what is the value if you put `=CELL("width", D2)` into a cell? Microsoft [says](https://support.office.microsoft.com/en-gb/article/CELL-function-b98b713d-d3de-4148-829f-80c886af6410?CorrelationId=8823cefa-f163-4513-af62-46fce0570f18&ui=en-US&rs=en-GB&ad=GB) that "width" gives the "Column width of the cell, rounded off to an integer. Each unit of column width is equal to the width of one character in the default font size.". – Wai Ha Lee Feb 18 '15 at 22:27
0

Both of the points in nbastos's answer were accurate and useful to get a working solution:

  • Newer versions of Office do spill into two cells, so the INDEX function to get the first element of the array addresses the problem satisfactorily.
  • I too found that, even tho my workbook was in Auto-calc mode, it had to be forced to recalculate using F9 from the keyboard (or in my case, via the .Calculate method in VBA). Evidently the Cell() function isn't a Volatile function with the "width" parameter.
E_net4
  • 27,810
  • 13
  • 101
  • 139
Steve Buckley
  • 1
  • 1
0

To handle the spill feature, you can put @ in front of CELL: @CELL("width",b2)

Dylan Dodd
  • 1
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 11 '21 at 10:31
-1

I found in "Excel for Office 365", Wai Ha Lee formula spills over into adjacent cell, because =CELL("width") returns array of 2 values (the width and a boolean).

This "spill" is a new feature, which can be avoided by using INDEX to use only first value in array. The below works for me in Office 365:

= IF(INDEX(CELL("width", B2),1) = 0, 0, B2) + IF(INDEX(CELL("width", C2),1) = 0, 0, C2) + IF(INDEX(CELL("width", D2),1) = 0, 0, D2)

Note: hiding columns using "Group" automatically recalculates formulas, but in my version, manually hiding columns does not trigger recalculation of formulas (it requires manual F9 trigger or other cell change).

nbastos
  • 1