I'm trying to create a custom function that will return if a cell is hidden or not. I have it working with just a single cell being called
Please Login or Register to view this content.But I need to get the function to also work with a range likePlease Login or Register to view this content.
Basically the same way that the Row() function worksPlease Login or Register to view this content.
What is it supposed to return when you pass a range and some cells are hidden and some not?
Entia non sunt multiplicanda sine necessitate
An array? of true/false values. If I call Row(A3) i get 3, if i call Row(A3:A4) i get 3,4 being return
That's easy enough to do, but what's the advantage of returning an array versus just dragging the formula =IsHidden(A3) right and/or down?
The disadvantage of an array formula is that you can't insert rows or columns interior to the array. Not a big thing, but here there's no reason for the inconvenience.
Last edited by shg; 02-23-2009 at 06:39 PM.
So I can use this function with Conditional Formatting. I want to automate alternative rows being highlighted and currently any hidden rows will mess this up using the =IF(MOD(ROW(), 2) = 1) formula, instead id need to do, =IF(MOD(COUNTIF(IsHidden($A2:D2), "= 1"), 2) = 1)
And I'd like it to still work with a single cell just like row() does
Alas, you can't use array formulas in conditional formatting.
You might use this:
Then in A1, CF Formula is =MOD(nVis(A$1:A1), 2)=1 and copy formatting down.Please Login or Register to view this content.
What do you mean you can't use array formulas in Condition Formatting? row($A$1:$A1) works just fine
Sorry, brain cramp. Go for it.
I still don't know how to actually implement the function =\
Did you try the one I provided?
Ah, I just saw it as a diff function hahaOk, so I tried it and.. technically it works, but its prohibitively too slow to even use. So, you were right and wrong about them not working
Here's an array verson of your function. I'm not sure what problem it solves.
Please Login or Register to view this content.
Last edited by shg; 02-24-2009 at 06:26 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks