Jeffrey Mahoney
Well-known Member
- Joined
- May 31, 2015
- Messages
- 2,742
- Office Version
- 365
- Platform
- Windows
I searched the internet to find a way to get the status of a column to determine if the column is collapsed in a group or manually hidden. MS needs to create a new function =COLUMNHIDDEN() and ROWHIDDEN().
This formula uses the CELL function to return the width. Unfortunately CELL now returns an array with the Width of the column and a Boolean value for the Default width (Office 365). I needed only the column width. If zero, the the column is hidden. I used the LARGE function to return the first part of the array (Width). I used INDEX to get the cell I need by providing the the entire row 1 and the column I wanted to test ($A6).
=IF(LARGE(CELL("width",INDEX(MasterData!$1:$1,1,$A6)),1)=0,"Hidden","Visible")
Be aware: Excel does not recalculate formulas when you manually hide or unhide a column. It will recalculate when you collapse or expand a column group.
Jeff
This formula uses the CELL function to return the width. Unfortunately CELL now returns an array with the Width of the column and a Boolean value for the Default width (Office 365). I needed only the column width. If zero, the the column is hidden. I used the LARGE function to return the first part of the array (Width). I used INDEX to get the cell I need by providing the the entire row 1 and the column I wanted to test ($A6).
=IF(LARGE(CELL("width",INDEX(MasterData!$1:$1,1,$A6)),1)=0,"Hidden","Visible")
Be aware: Excel does not recalculate formulas when you manually hide or unhide a column. It will recalculate when you collapse or expand a column group.
Jeff