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" wrote in message
...
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" wrote in message
...
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