We help IT Professionals succeed at work.

Copy N consecutive visible rows from filtered data to another worksheet

Hello,

I am looking for a formula that will copy a visible cell from a filtered dataset in another worksheet (see attached sample for filtered example).  

I want to create a table in another worksheet that contains cell ($A$1,1) to cell ($A$1+4,1) from the filtered data where N refers to another cell that contains which visible row to start from.

For example:
if $A$1 contains the value 3, then the first cell in the new table would contain the 3rd visible item in column 1 from the filtered data.  The second cell in the new table would contain the 4th visible item in column 1 from the filtered data, etc.

Hopefully that makes sense.
Comment
Watch Question

NorieAnalyst Assistant

Commented:
No attachment I'm afraid.
Rob HensonFinance Analyst

Commented:
Does it have to be formula based?

When you apply a filter to a dataset, if you then copy the visible data and paste it elsewhere only the visible rows will be pasted.

Alternatively, Advanced Filter function can copy filter results to another location; can be on another sheet if so required.

Thanks
Rob H

Author

Commented:
Sorry about not including the attachment.    Should be there now.

The reason that I am looking to use a formula is that I am graphing the data and using a slider to dynamically update the data to graph only a portion of the visible dataset.  The slider controls the start position for viewing the visible data.  I.e.  I am graphing visible rows 1-10 or 2-11 or 3-12, etc.
Filtered.png
Rob HensonFinance Analyst

Commented:
I am not aware of doing it the way you seem to want to do.

However, are you aware that graphs will only show data from visible rows anyway. For example, if you have a chart showing a full years worth of columns but only up to month 10 is populated and you only want up to month 10 on the chart, hiding columns 11 & 12 of the data will hide them on the chart.

Alternatively, copy the filtered data as I suggested and then extract a subset of data from the filtered data using INDEX formulas.

=INDEX(DataRange,RowNumber,ColumnNumber)

DataRange - The copied Filtered data
RowNumber - The row number of the required start, increment this for the number of rows of data required
ColumnNumber - The column from which data is being extracted

Then build the chart on the subset rather than the original set.

Alternatively, I don't know much about them but you might be able to use Pivot Chart. This will extract from the original source data and you can apply filters in much the same way but I don't hink you will then be able to say rows 11-20 for example, unless rows 11-20 match a further criteria that you can filter on.

Thanks
Rob H
Yes, I am aware that the graphs will only use the visible data.  The problem with doing a manual cut and paste is that the dataset is very large and in order to effectively use the charts, I can only plot about 20 lines at a time out of 2317.  That is why I needed to use a scroll bar on the chart to view 20 lines at a time.

I was finally able to figure out a method to accomplish this:

1) I added a "Visible" column (A) to the dataset with the formula: =(AGGREGATE(3, 5, AE1)>0)+0 where AE1 is simply a column in the data containing a number.  If the row is visible, the cell will read 1, otherwise 0

2) In the second worksheet with the chart I entered the following formula in cell A1 and copy for 20 rows:  =IFERROR(SMALL(IF(Data!$A$2:$A$2317=1,ROW(Data!$A$2:$A$2317)-MIN(ROW(Data!$A$2:$A$2317))+1),ROW(A1)-1+$F$1)+1,"").  Cell F1 is controlled by the scrollbar attached to the chart so if it at the beginning of the scrollbar, then F1=1.

3) In cell B1, I used the formula:  ="Data!B"&$A1.  

4) In cell C1, I used the formula: =IFERROR(INDEX(INDIRECT($B1),1),"") to pick the column referenced by the formula in B1 which refers to the original dataset.

5) I was then able to graph the 20 datapoints in cells C1 to C20

Hopefully this makes sense.

Author

Commented:
I was able to find a solution myself using other forums.