Advanced Filter for 2 separate lists

[zest1]

Hi,

I have 2 lists on Sheet2 each having a different number of columns and headings. I'm trying to use AdvFltr to copy one OR the other list to Sheet1 into a common group of cells, based on a criteria range in E2 & F2.

In other words, having one group of cells recieve which ever list I select via a command button. I played around with some code, pasting the headings row and running the AdvFltr, but its just a start.

Any help will be greatly appreciated.

Thanks.

(I tried to attach a sample file, but oddly there's no link to do so).

[malik641]

Hi Zest1,

When you make a post, do you see the "Additional Options" frame? There is a button "Manage Attachments" where you can add your file, which would really help in this case (at least for me).

[zest1]

finally able to attach a sample file


(forum administrators - the file attachment button on the posting page does not display using my Opera browser, but it displays ok using my Firefox browser)

[zest1]

[seem to be having problems even using firefox - whenever I click submit, I repeatedely get asked to log on again - and the file did not get attached - also, getting an error message pertaiing to this site about "Database experiencing problems]


trying once again

[malik641]

Zest,

Your attachment went through on your third attempt. And just so you know, you should post your problems here.

Anyway, I'll take a look at this for you

[malik641]

Hey Zest,

Could you provide some more info on what you want to do exactly? What are you using Advanced Filter for? Do you just want to populate Sheet1 with one of the tables with whichever button you press? You don't need Advanced Filter to do that. And what's with the Qty cells???? Sorry, but I seriously need some more info.

[zest1]

thanks Joseph.

The idea is to conserve space by using the same group of cells on Sheet1 to display the search results of whichever list I've selected.

I'd like to enter a search criteria range (ie. 25 & 30) in E2 & F2 and then click the button associated with the particular list to search, and have the results that fall within the search criteria show up starting in A11 on Sheet1.

The tricky part is that each list has different headings, and different search criteria. Also one list has 4 columns and the other has 6.

Hope this helps clarify it a bit better.

[zest1]

Any suggestions how to do this?

[malik641]

thanks Joseph.

The idea is to conserve space by using the same group of cells on Sheet1 to display the search results of whichever list I've selected.

I'd like to enter a search criteria range (ie. 25 & 30) in E2 & F2 and then click the button associated with the particular list to search, and have the results that fall within the search criteria show up starting in A11 on Sheet1.

The tricky part is that each list has different headings, and different search criteria. Also one list has 4 columns and the other has 6.

Hope this helps clarify it a bit better.

Okay, I'm guessing the Qty cells are a >= and <= (between and including).

I would suggest creating named ranges for each set of headings in each table. This will help with obtaining the 4 or 6 columns desired.

Then I would set the code up to find the last row of either table (whichever button is selected). Then I would set that as the advanced filter range. I would also use the Qty cells as the criteria range (do not forget to place >= and <= when using advanced filter to look for values including and between).

...just to get you started

[zest1]

Still having problems.

I have the code executing fine, except it returns all the records instead of only the ones set within the criteria range.

Also, after the code runs, some of the copied headings on sheet1 do not show up unless I click in those cells to refresh them. And, the cell 'positions' that were copied from Sheet2 are somehow seeping through and being highlighted on sheet1. I can easily solve the issue by having the code make another cell active when it ends, but there's obviously a problem.

I added a couple dropdowns to make things easier as you can see in the attached file.

Can you please check it out and tell me why it's still not working properly.

Thanks a lot