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

M

Marco

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
 
B

Bernie Deitrick

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
 
M

Marco

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
 
B

Bernie Deitrick

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
 
Top