Define a range containing the first 10 rows of a filtered list

I have a list of about 200 rows with filters. I need to build a graph with
series referring to that list but displaying only the 10 first (visible) rows
(which vary depending on the filters I set).
I tried to use a named range with offset(...10) but Excel considers the
hidden rows which makes that if the first 10 rows are not visible because
filtered, my graph is empty !
Thanks in advance

Marco,

You need a helper column of formulas. Let's say that your list is in column B, with B1 as a header,
and the items start in B2. In cell A2, use the formula

=SUBTOTAL(2,$B$2:B2)

and copy down to match your column B.

Then to get your ten values, use a VLOOKUP formulas, like this:

=VLOOKUP(Row(A1),A$1:B$200,2,False)

copied down for ten rows.

HTH,
Bernie
MS Excel MVP


"Marco" <[email protected]> wrote in message
news:[email protected]...
>I have a list of about 200 rows with filters. I need to build a graph with
> series referring to that list but displaying only the 10 first (visible) rows
> (which vary depending on the filters I set).
> I tried to use a named range with offset(...10) but Excel considers the
> hidden rows which makes that if the first 10 rows are not visible because
> filtered, my graph is empty !
> Thanks in advance

Hi Bernie, thanks for your post but I don't think it matches my needs (or I
don't know how handle it..) I'll try to give a simple example. Below is my
data sheet:

I want to display a simple bar graph with col. A and B and there is a filter
in column C.
- In the graph definition under "Source data" ---> "Series", what range can
I input in order to display maximum only the first 3 values that we can see
in the sheet ?
If I define : $A$2:$A$4 and $B$2:$B$4 it would be fine. But now I filter
col. C with Year=2003 and my graph only displays values of row # 3 and I want
to see also rows # 6 and 7.

A B C
1 name Sales Year
2 John 70.00 2005
3 Marc 53.00 2003
4 Annie 42.00 2005
5 Jan 37.00 2004
6 Pat 14.00 2003
7 Nicole 8.00 2003

Thanks !
Marco


"Bernie Deitrick" wrote:

> Marco,
>
> You need a helper column of formulas. Let's say that your list is in column B, with B1 as a header,
> and the items start in B2. In cell A2, use the formula
>
> =SUBTOTAL(2,$B$2:B2)
>
> and copy down to match your column B.
>
> Then to get your ten values, use a VLOOKUP formulas, like this:
>
> =VLOOKUP(Row(A1),A$1:B$200,2,False)
>
> copied down for ten rows.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Marco" <[email protected]> wrote in message
> news:[email protected]...
> >I have a list of about 200 rows with filters. I need to build a graph with
> > series referring to that list but displaying only the 10 first (visible) rows
> > (which vary depending on the filters I set).
> > I tried to use a named range with offset(...10) but Excel considers the
> > hidden rows which makes that if the first 10 rows are not visible because
> > filtered, my graph is empty !
> > Thanks in advance
>
>
>

Marco,

With your example table in A1:C7, insert a new column A, moving your table to B1:D7.

Then in cell A2, enter the formula

=SUBTOTAL(2,$D$2:D2)

and copy to A3:A7.

Then in B10, enter the formula
=VLOOKUP(ROW(A1),$A$1:$D$7,COLUMN(B1),FALSE)
and copy to B10:D12

Then base your graph on the new table of formulas in B10:D12.

Before you apply the autofilter to your initial list, select A1:D8 (select an extra, blank row at
the bottom) - sometimes, Excel is funny about handling the last row when filtering lists that
include SUBTOTAL formulas.

HTH,
Bernie
MS Excel MVP


"Marco" <[email protected]> wrote in message
news:[email protected]...
> Hi Bernie, thanks for your post but I don't think it matches my needs (or I
> don't know how handle it..) I'll try to give a simple example. Below is my
> data sheet:
>
> I want to display a simple bar graph with col. A and B and there is a filter
> in column C.
> - In the graph definition under "Source data" ---> "Series", what range can
> I input in order to display maximum only the first 3 values that we can see
> in the sheet ?
> If I define : $A$2:$A$4 and $B$2:$B$4 it would be fine. But now I filter
> col. C with Year=2003 and my graph only displays values of row # 3 and I want
> to see also rows # 6 and 7.
>
> A B C
> 1 name Sales Year
> 2 John 70.00 2005
> 3 Marc 53.00 2003
> 4 Annie 42.00 2005
> 5 Jan 37.00 2004
> 6 Pat 14.00 2003
> 7 Nicole 8.00 2003
>
> Thanks !
> Marco
>
>
> "Bernie Deitrick" wrote:
>
>> Marco,
>>
>> You need a helper column of formulas. Let's say that your list is in column B, with B1 as a
>> header,
>> and the items start in B2. In cell A2, use the formula
>>
>> =SUBTOTAL(2,$B$2:B2)
>>
>> and copy down to match your column B.
>>
>> Then to get your ten values, use a VLOOKUP formulas, like this:
>>
>> =VLOOKUP(Row(A1),A$1:B$200,2,False)
>>
>> copied down for ten rows.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Marco" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have a list of about 200 rows with filters. I need to build a graph with
>> > series referring to that list but displaying only the 10 first (visible) rows
>> > (which vary depending on the filters I set).
>> > I tried to use a named range with offset(...10) but Excel considers the
>> > hidden rows which makes that if the first 10 rows are not visible because
>> > filtered, my graph is empty !
>> > Thanks in advance
>>
>>
>>