Copy N consecutive visible rows from filtered data to another worksheet


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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
No attachment I'm afraid.
Rob HensonFinance AnalystCommented:
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.

Rob H
mthiel3333Author 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.
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Rob HensonFinance AnalystCommented:
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.


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.

Rob H
mthiel3333Author Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mthiel3333Author Commented:
I was able to find a solution myself using other forums.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.