Offset to ignore hidden cells not working in 2007

[JoseBagg]

SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)

This formula is from Domenic. It is part of the formula used to su
data while ignoring hidden rows. However, this part is not working fo
me in excel 2007.

Highly likely, that I am the cause of the problem. Does anyone hav
any help for me?

 

[T. Valko]

this part is not working for me in excel 2007.

SUBTOTAL(3...

That would only work for rows that are hidden by using a filter. Try using:

SUBTOTAL(103...

The 100 series arguments are for rows that are hidden by some other means
like hiding them manually or hiding them with a macro procedure.

If that doesn't solve the problem then you'll have to post the entire
formula and explain in more detail what you're trying to do.

--
Biff
Microsoft Excel MVP

SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)

This formula is from Domenic. It is part of the formula used to sum
data while ignoring hidden rows. However, this part is not working for
me in excel 2007.

Highly likely, that I am the cause of the problem. Does anyone have
any help for me?


--
JoseBagg
------------------------------------------------------------------------
JoseBagg's Profile: 1591
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=185669

Excel Live Chat

 

[JoseBagg]

=SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),--(L2:L100="Open"))

This is the larger formual posted by Domenic in another thread.

The part of the this formula that is supposed to create an array of 1's
and 0's where 1 indicates a visible cell and zero indicates a hidden
cell is

=(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1))


However, when I evalute this in excel 2007, it does not evaluate to a
nice array like it should, it just evaluates to a single number.

 

[T. Valko]

The part of the this formula that is supposed to

create an array of 1's and 0's where 1 indicates
a visible cell and zero indicates a hidden cell is
=(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1))
However, when I evalute this in excel 2007, it
does not evaluate to a nice array like it should,
it just evaluates to a single number.

If you enter that formula in a single cell then it will return only a single
result. That part of the formula is processing the arguments as an ARRAY. To
see each result of the array you'd need to select a range of cells the same
size as L2:L100 then enter the formula as an array.

If you want to see the array without having to enter the formula in a bunch
of cells, highlight that portion of the formula *in the formula bar* then
press function key F9.

Another option is to select the formula cell>goto the Formulas tab>Formula
Auditing>Evaluate Formula. Click the Evaluate button repeatedly to see how
Excel calculates the formula one step at a time. I use this feature often so
I added this to my QAT.

--
Biff
Microsoft Excel MVP

=SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),--(L2:L100="Open"))

This is the larger formual posted by Domenic in another thread.

The part of the this formula that is supposed to create an array of 1's
and 0's where 1 indicates a visible cell and zero indicates a hidden
cell is

=(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1))


However, when I evalute this in excel 2007, it does not evaluate to a
nice array like it should, it just evaluates to a single number.


--
JoseBagg
------------------------------------------------------------------------
JoseBagg's Profile: 1591
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=185669

Excel Live Chat

 

[JoseBagg]

Thanks, I get it now.

 

[T. Valko]

You're welcome!