Formula Filter Issue

**wacko1979** · 11-06-2018, 10:59 AM

Hi All, first time poster. Having what is a major issue to me , but probably something pretty simple to others.
I have an excel file and am using data from 2 separate worksheets. I have applied a filter to 1 worksheet, to only allow the points I require to be brought to the other worksheet be visible. I then go to the worksheet that I wish to bring the points to, and simply insert a formula [ ='Points List'!A18 ]. This works fine, until I try to drag the formula to cells below. When I drag the formula, it picks up all the cells in the other worksheet.
There is no logic to what cells will be filtered, so I cannot work out a system to overcome this. I do not want to copy and paste, as the values will be changing.

Any help greatly appreciated.

**José Augusto** · 11-06-2018, 11:44 AM

Hi

The condition you are using to filter the data must be used on both sheets.

**Aardigspook** · 11-06-2018, 11:53 AM

Try this - maybe it does what you want:

1. Filter your source data.
2. Now copy the filtered data.
3. Go to your other sheet.
4. Click the cell at the top-left of where you want the filtered data to go.
5. Click Paste Special (use the drop-down arrow below the big Paste button).
6. Click Paste Link (bottom left).

Does that work for you?

**wacko1979** · 11-07-2018, 05:55 AM

Thanks for the responses, that got some of the issue resolved. I have now been presented with a greater issue. My source data is now complete, listing off approx 200 cells. I want to insert (or use an = formula) that into another worksheet, where I have filtered the rows. Due to the filter being in place on my destination, when I paste, or try to use a formula, it applies the paste or formula to all cells in range, resulting in the data being included in incorrect cells. Any ideas?

**Aardigspook** · 11-07-2018, 08:16 AM

If you want to copy a filtered sub-set of one range and paste it into a filtered sub-set of another range, I'm 99% sure that will need VBA.

It will be easier to work out what you are requesting if you upload a small sample file (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a before sheet and an after sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitise the data - replace confidential information with fake information or junk data (depending on whether the structure of the cell contents is vital to the process or not).
Click on Go Advanced and then scroll down to Manage Attachments to open the upload window.

**wacko1979** · 11-07-2018, 08:37 AM

See attached. Test template worksheet has to follow a sequence as per the detail available for each item, it may have 1 point or up to 3. Database worksheet has the compiled data I will be utilising in correct sequence.
I filter the test template to show the point test only, insert [=Database!C3], and attempt to drag. I want it to drag to show the sequence as per the database, C1 C2 C3 C4 C5 etc.
File shows how I want it to be completed, it does not work like this when I try to drag cells.