Hi,

Is there a formula that will notify me if any column(s) in a range are
hidden? (I don't want to do this by VBA.)

I want it to notify me by formula, if that's possible, because I need to
know immediately if something is hidden. (Once I can understand how to
detect a hidden column, I'll probably use conditional formatting to make
the cell turn red to warn me.)

The following formulas worked with rows, when the cells A2-A5 had values in
them. However, I can't make them work by changing "ROWS" to "COLUMNS" and,
for example, "A2:A5" to "A2:G2".

When using a filter
=2*ROWS(A2:A5)-(SUBTOTAL(3,A2:A5)+SUBTOTAL(103,A2:A5))

Without a filter
=SUBTOTAL(3,A2:A5)-SUBTOTAL(103,A2:A5)

(Credits to JMB for the formulas that worked with rows. See the thread with
the subject. "Detect Hidden Rows by Formula.")

Thanks,

Dave

--
Please delete "ANTI-SPAM" from email address (E-Mail Removed)
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

I would say no using just built in functions (without using values of the
cells - example using nothing like all columns and only columns with 3 in
row 2 are hidden)

--
Regards,
Tom Ogilvy


"David Godinger" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> Is there a formula that will notify me if any column(s) in a range are
> hidden? (I don't want to do this by VBA.)
>
> I want it to notify me by formula, if that's possible, because I need to
> know immediately if something is hidden. (Once I can understand how to
> detect a hidden column, I'll probably use conditional formatting to make
> the cell turn red to warn me.)
>
> The following formulas worked with rows, when the cells A2-A5 had values
> in
> them. However, I can't make them work by changing "ROWS" to "COLUMNS" and,
> for example, "A2:A5" to "A2:G2".
>
> When using a filter
> =2*ROWS(A2:A5)-(SUBTOTAL(3,A2:A5)+SUBTOTAL(103,A2:A5))
>
> Without a filter
> =SUBTOTAL(3,A2:A5)-SUBTOTAL(103,A2:A5)
>
> (Credits to JMB for the formulas that worked with rows. See the thread
> with
> the subject. "Detect Hidden Rows by Formula.")
>
> Thanks,
>
> Dave
>
> --
> Please delete "ANTI-SPAM" from email address
> (E-Mail Removed)
> David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr.
> Martin King

hi, David !

> Is there a formula that will notify me if any column(s) in a range are hidden? (I don't want to do this by VBA.)
> I want it to notify me by formula, if that's possible, because I need to know immediately if something is hidden.
> (Once I can understand how todetect a hidden column, I'll probably use conditional formatting to makethe cell turn red to warn me.)...

it's just a wild idea... if you can use a 'helper row', let's say: row 1 ?

[A1] =cell("width",a1) <- copy this formula up to cell 'G1'

now, use in the 'warning-cell'... -> =countif(a1:g1,"<1")

one 'condition'... a re-calculation of your model is needed in order to update the 'warning-cell' :-(

you could use conditional format to 'hide' [A1:G1]... hide the entire row... or ???

as I said, it's just a wild idea, but -I guess- you can 'handle' -almost-hidden columns :-)

hth,
hector.

Thanks, but I have trouble following the language. How would a sample
formula look?

"Tom Ogilvy" <(E-Mail Removed)> wrote:

>I would say no using just built in functions (without using values of the
>cells - example using nothing like all columns and only columns with 3 in
>row 2 are hidden)
>
>
>"David Godinger" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>>
>> Is there a formula that will notify me if any column(s) in a range are
>> hidden? (I don't want to do this by VBA.)
>>
>> I want it to notify me by formula, if that's possible, because I need to
>> know immediately if something is hidden. (Once I can understand how to
>> detect a hidden column, I'll probably use conditional formatting to make
>> the cell turn red to warn me.)
>>
>> The following formulas worked with rows, when the cells A2-A5 had values
>> in
>> them. However, I can't make them work by changing "ROWS" to "COLUMNS" and,
>> for example, "A2:A5" to "A2:G2".
>>
>> When using a filter
>> =2*ROWS(A2:A5)-(SUBTOTAL(3,A2:A5)+SUBTOTAL(103,A2:A5))
>>
>> Without a filter
>> =SUBTOTAL(3,A2:A5)-SUBTOTAL(103,A2:A5)
>>
>> (Credits to JMB for the formulas that worked with rows. See the thread
>> with the subject. "Detect Hidden Rows by Formula.")

--
Please delete "ANTI-SPAM" from email address (E-Mail Removed)
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

Hi Héctor,

The formula does what you said. Too bad a recalulation is required.

By the way, I didn't understand what you said about using conditional
format to hide the row. And what is "bth" at the end?

Thanks!

Dave


"Héctor Miguel" <(E-Mail Removed)> wrote:

>hi, David !
>
>> Is there a formula that will notify me if any column(s) in a range are hidden? (I don't want to do this by VBA.)
>> I want it to notify me by formula, if that's possible, because I need to know immediately if something is hidden.
>> (Once I can understand how todetect a hidden column, I'll probably use conditional formatting to makethe cell turn red to warn me.)...
>
>it's just a wild idea... if you can use a 'helper row', let's say: row 1 ?
>
>[A1] =cell("width",a1) <- copy this formula up to cell 'G1'
>
>now, use in the 'warning-cell'... -> =countif(a1:g1,"<1")
>
>one 'condition'... a re-calculation of your model is needed in order to update the 'warning-cell' :-(
>
>you could use conditional format to 'hide' [A1:G1]... hide the entire row... or ???

Huh?

>as I said, it's just a wild idea, but -I guess- you can 'handle' -almost-hidden columns :-)

What's an "almost-hidden" column?

>hth,
>hector.
>

--
Please delete "ANTI-SPAM" from email address (E-Mail Removed)
David Godinger: Go player, student of Mahatma Gandhi, the Buddha, and Dr. Martin King

hi, David !

> The formula does what you said. Too bad a recalulation is required.
> By the way, I didn't understand what you said about using conditional format to hide the row.
> And what is "bth" at the end?

I'm sorry, my mistake, I didn't want you to understand as really hiding the entire row
only the content of the cells with the function cell(...)

hth is an acronym of Hope This Help
BTW would be it of By the way...

columns -almost- hide has been because the function cell ("width") returns the width of the column in points rounded to integer
that is to say, if you adjust the width of a column i.e. to 0.5 it would be detecting by <1 as if really were a 'hidden' column

regards,
hector.

__ previous posts __
>>you could use conditional format to 'hide' [A1:G1]... hide the entire row... or ???
>
> Huh?

>>as I said, it's just a wild idea, but -I guess- you can 'handle' -almost-hidden columns :-)
>
> What's an "almost-hidden" column?