LinkBack URL
About LinkBacks
Find attached , required formula on A7 to drag rightwards and down words
Find attached , required formula on A7 to drag rightwards and down words
Raw data on sheet2 to pull on sheet 1 depening on what I require to search for
Expected output
- If I types tiles on b2 , it should pull results from sheet 2 with the word tiles on column d ,since I am expectig to put up sanitary and fittings
- If I type gemma on b1 , it should bring all tiles of this supplier
- If then I type 50 x 50 on cell b4 whil having gemma on cell b1 , i should bring those tiles sized of that supplier
Alving Chung I hope you have done this for me for tile size and tile surface , two levels , I guess 4 levels wont be an issue too
This is definately an array formula
Try this automated version...
Regards,
Rudi
Wow rudi , can you please explain how you did this
My boss will go nuts seeing this , we talked about last eve and he gave me a week
on over while I have the answer ready this morning
Do i save it macro enabled , this is super , but I only fear asking them how to enable macro
I wish I had a formula version too
Hi,
glad I could help
The filter is driven by VBA code that triggers when you click the Filter button.
If you press ALT+F11, you will see the code that runs the filters.
The cells in B2:B4 have Data Validation (Data Ribbon) that links to a unique list of items for that column. (See Sheet 2, Columns P to S)
Once you have chosen your combination of values, and click the filter button, the code does the rest, to filter for your values and copy the filtered data from Sheet 2 to Sheet 1.
Yes...if there is macros involved, it has to be saved as (*.xlsm), else the macros will not trigger.Originally Posted by makinmomb
A formula version will become quite slow on a big file. I assume you have more data than just the sample data you posted. If formulas are to run this filter, it will become quite slow with larger volumes of info as the will need to vlookup each value from each column of data...
Maybe someone else can jump in with a formula version... (Not me though)
RUDIS if I am planned to go with your solution , is the vba set to trigger more data , meaning if I add on additional stuff on the raw data sheet
Or do you have to modify the vba
Hi,
You can add as much data as you want to the list in sheet 2. The macro will filter the entire contents no matter how long (or short) the list is.
Please note that you must NOT have any completely blank rows between the data. Full blank rows will cause the macro to not work properly.
You will need to maintain the drop down lists manually (See image below)
Also, as you add more data to the list in sheet 2, you must add the unique values to the small lists in columns (P to S)
Then on sheet 1 you must select cell b1 and go Data, Validation, and update the range to include the new data on sheet 2, else the new values will not display in the drop downs on sheet 1. Do this for each of the four cells B1:B4 (if you added new values to the small lists on sheet 2)
Hope that is clear...
Yes the data validation I got you , thank you very much , while I enjoy praises pouring
onto my mail , courtesy definately yours
One quick clarification , will it still work , if I remove the drop menu ,
we have too many products , does the vba still remain same ?
I wish to type instead of choose from the drop menu
Yes... the code is not dependent on the drop downs.
Even if you remove the validation, you can still type the criteria in the cells B1:B4 and the macro will work.
RUDIS one more favour , can you please re send me the file , search option added ,
search by code , i forgot to mention that , please place that on B5
This means it will bring one result e.g. if suppose i say 239.000.01
Hi,
Attached is the updated version.
Thank you wow really made it a difference on this , I have a line of employees who
want to shake hands , they have scattered files , this will be one stop pricelist , hurray
Thank you again Rudis
My pleasure...
I realised i may have to add columns on the raw data , do I have to contact you for changes
or will you explain what to do what on the vba
I plan to add two columns offer price after price and % column as to how much is that product offered at
I can modify the macro to work dynamically on any size list, no matter how many columns or rows the source list will be.
I will send the new "low maintenance macro soon, however one or two questions to ask..
1. Are you still using the validation dropdown arrows, or can I scrap them?
2. The macro can be dynamic, but the filters cannot...will the list remain with only the current 5 filter, or must the new columns also have a filter?
TX
No i have scrapped the dropmenu , the team is old fashioned , they punch text
The filters will remain the same
The only major change now is two columns add OFFER going after price and % after offer ,
that is how much percent is that e.g. 20% and so on
OK...
I'll update the code...
Hi,
Here is the updated workbook.
I have removed the validation and changed the code to work dynamically on the size of the list on sheet two. No matter how narrow or wide, tall or short it becomes, it will always work on the table dimensions and return the filtered data to sheet 1 based on that size...
Attached...
Thank you Rudis , I think we can mark the thread solved .
I was wondering if the price of an item on the raw data changes , is it possible to have a mirror
log of what changed only , a summary on sheet 3 , new price with comments what it was before
The reason I am not starting a new loop is because , if I get a vba answer plus your vba , I think
it will collide
I am smiles at your vba I must say
Do you want the entire record copied to sheet 3 if the only the price changes, and next to the price (in a separate column, the old price before it was changed?
Not that Excel events can only trigger after something has changed, so I'll have to figure out a pre-trigger action to capture the price before it was changed. Give me a bit more ideas of what you envision so I can clarify a plan.
I need only what changed , not the whole pricelist , we have too many products , I have started a separate thread on it too
In case I get a formula on it would be cool , since than the vba remains unchanged
The new thread is called price change summarise
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks