Filtered table from another sheet

[Deividas]

Hello gang, new member here seeking for your wisdom!

I have been given an exam task in my university and I will try to explain it to you so don't flame for my non-fluent English .

What I need to do is to think of an real activity that would be automated using Excel.
I.e. we have a shop which is specified into selling office stuff like pencils, pens etc. and what we need to do is to make a drop-down list with all those items. Then it is made in an example with quantity. A spin-button was used to select quantity. After that an item is listed in shopping cart as well as its quantity and overall price by the formula (quantity * price). After you have all items you need in your shopping cart, you can move forward and receive your bill. That's the basic idea given in an example which is not very informative so I'll try to explain what I did and what I am struggling with since my excel document is not in English there will be no benefit if I would upload it.

My idea is very simple - a laptop shop. I've made a drop-box list with manufacturers (1), processors (2), video cards (3), screen size in inches (4), RAM (5), HDD size (6), and price (7). Under the company name I've made a filter criteria (8). Kinda simple but it took me like 6 hours in total . Now what I need to do next is to filter my laptop table and paste it from sheet2 to sheet1 using those filters. I.e. I have chosen manufacturer ASUS with a processor CELERON, so from original table with all ASUS laptops a specific command (or macro) should do the filter according to filter criteria (8) and to paste it to my sheet1.

I hope you got the basic idea, if not, I'll try to explain it in more details. Also, attaching a picture with stuff that might help understanding my task.

http://i.imgur.com/Ls2vtFR.jpg

Cheers!

 

[bobhc]

Good day Deividas and welcome to the forum please fread this link as it will help you in using the forum

http://chandoo.org/forum/forums/new-users-please-start-here.14/

so don't flame for my non-fluent English

Your command of the English grammer is better than some who where born to it, you have no need to apoligize.

As to your question if you use the "Upload a File" button at the bottom right of the question box to upload a sample file of what you ahve done it will be easyer for members to offer help.

.

 

[Deividas]

Thank you for your fast response!
I was hoping I could avoid uploading the file since it is not in English, but if it will help to solve my problem, then sure!

 

[Aurelie]

Hi Deividas,

I think I understand what you mean (even with your excel file in Lithuanian ) I developped something similar at work, but only with 4 criteria, and it was already pretty complicated as you have to foresee all possible combinations. What I did was to create a User Defined Function with nested select - case which would return the item corresponding to all selected criteria. With 4 criteria, I had to enter 32 possible combinations to cover all possibbilities. I let you imagine how many combinations you would have to foresee to cover 8 criteria...

There are maybe other solutions out there, unfortunately I can only propose you this one.
Would you be able to reduce the number of conditions, or do you have to stick to 8?

Hope I don't answer too late.
Cheers,

 

[Deividas]

Thank you for your reply. I've been working on my project these past few days by googling, youtubing, etc. so I'll upload updated worksheet. For now I have a problem which contains Filter button. What I need to do is to filter a table using criteria above it and I am using macro to do Advanced Filter, but it only filters lines that are visible. Lets say I want to filter by Asus manufacturer. It will filter it only then, when all Asus criteria is visible in the table. If I would scroll down and Asus wouldn't be shown, it will not filter it, just show a blank table.
Any ideas how to filter it despite that criteria has to be visible in table?