Offset to ignore hidden cells not working in 2007
in [Functions]
|
From: JoseBagg on 8 Mar 2010 13:09 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 [url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url]
From: T. Valko on 8 Mar 2010 13:58 >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 "JoseBagg" <JoseBagg.47iow2(a)thecodecage.com> wrote in message news:JoseBagg.47iow2(a)thecodecage.com... > > 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 > > [url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url] >
From: JoseBagg on 8 Mar 2010 14:10 =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 [url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url]
From: T. Valko on 8 Mar 2010 14:43 >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 "JoseBagg" <JoseBagg.47irnj(a)thecodecage.com> wrote in message news:JoseBagg.47irnj(a)thecodecage.com... > > =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 > > [url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url] >
From: JoseBagg on 8 Mar 2010 14:51
Thanks, I get it now. -- JoseBagg ------------------------------------------------------------------------ JoseBagg's Profile: 1591 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=185669 [url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url] |