Display Vendor Details

[MdYas]

Hell Experts,

If i select rice from the category drop down list list only rice related product to display in sub item drop down list.

After selecting product from sub item for example if i select 1121 golden sella rice i only want selected product vendor details to be display

Please find the attached copy for your reference.

I really appreciate the fact that I can come to you for this and I know you are the most qualified person for the project.

Please let me know if you can help.

I look forward to hearing from you.

Yaseen

[Kevin#]

Here is one way to do what you want.

Your vendor table has been extended to include column A, and formula =vLookUp used to look up the values

5 additional tables created (named "Category", "Rice", "Tea", "Banana", and "Spice"), and formula =Indirect used to create dropdown values

DataValidationFormula.jpg


NOTE - The tables have been created to allow additional items to be added without having to revise the formula (ranges within tables are "dynamic")

[MdYas]

Thanks for quick reply Mr. Kevin,

i'll check and come back to you if i have doubt

[Kevin#]

You are welcome.

The =Indirect formula uses the text value of a cell which must match the name of either a Table or NamedRange to provide the list of items for the dropdown

[MdYas]

ok i'll try if mind can you make for me in my attached file

[Kevin#]

the file attached to post#2 should contain everything you need
cells C3 to D6 already contain data validation and dropdowns

is there anything else?

[MdYas]

Dear Mr. kevin,

vendor details not displaying what i want is if i select sub item for example spice i want all spice vendors to be display and so on.

please see the below image what i want is to get all vendors of the selected product from the sub item!



Thanks in advanceScreenshot (76).png

[Kevin#]

Try this
Open the attached and allow macros
- Data validation & selection moved to sheet "Vendor details"
- Very simple macro added that uses "Advanced Filter" to select the suppliers matching criteria.
- Select using cells B2/C2
- Macro will list matching suppliers automatically when value in cell C2 changes
- to show all Vendors click on "Clear"

AdvancedFilter.jpg

Please Login or Register  to view this content.

NOTE
- range to row 9999 included to allow for lots more vendor details
- the value in C1 must match the heading in C5 for "advanced filter" to work

[MdYas]

thanks you so much for your quick reply, i'm getting error when i'm selecting the product in sub item drop down box.

please see the below image,

Screenshot (77).png

[Kevin#]

The error is happening because the sheet is already showing all the data.
So vba now checks whether all cells on the sheet are visible

Please Login or Register  to view this content.

[MdYas]

its working perfectly now thank you so much and i have more to ask you regarding this project.

you made my day sir.

[MdYas]

Dear Kevin,

can we make automatically product specification after selecting product form the sub item drop down also i want picture to display of the same product.

please find the attached copy for your reference

Thanks in advance.

[Kevin#]

this post deleted - having major problems with site today

[Kevin#]

Hopefully this provides you with a method to achieve what you want

In the attached workbook, try amending Cell C2 and see what happens
(valid dropdown values include SHARBATI RAW / STEAM, SHARBATI SELLA GOLDEN, SHARBATI WHITE SELLA, SUGANDHA GOLDEN SELLA)

Explanatory Notes
Product specification and photo included above the data – otherwise need to allow for variable number of rows
Your product specification is not complete and the product fields are inconsistent
For illustration, I have enabled the lookup for some items – I have used the products where the specificationfields are the same
You should decide which specification fields should appear on sheet “Vendor details” and ensure that those fields are present for every product (in sheet “Spec & Photos”) and include a value even if not relevant (“-“ or ”N/A” perhaps) so that you can see that all the fields are being pulled through
You may want a to use different set of specifications for each product range instead of having lots of empty values

LookUp Formulas
Formula used to find the correct photo (in cell D6):
Formula: Select Code copy to clipboard
=IFERROR(VLOOKUP(C2,'Photo Index'!A:B,2,0),"Not Found")

Formula to look-up the product specification (in cell G2, and copied down):
Formula: Select Code copy to clipboard
=IFERROR(VLOOKUP($C$2&F2,'Spec & Photos'!A:D,4,0),"Not Found")
This above lookup searches for the concatenation of cell C2 and cell F2, and looks for match in column A of Sheet “Spec & Photos” (see below***)

Amendments made to workbook
Sheet “Vendor details”
10 rows inserted at top of sheet to allow inclusion of product specifications and photo
lookup for photo added in D6
lookup for product specifications added in G2 to G10
photo added in H1
Sheet “Spec & Photos”
added 2 columns
Column B contains the Product name
***Column A is a concatenation of Column B and Column C used in lookup in cell G4 (sheet “Vendor details”)
Renamed all photos to make life easier
Sheet “Photo Index”
this is a new sheet
holds new list of products and related photo number
this is used in lookup in cell D6 (sheet “Vendor details”)

Macro to add the relevant photo
Select Code copy to clipboard

Please Login or Register  to view this content.

Amended change event macro
Data range starts at A15 (previously A5) to match insertion of rows to take product specifications and photo
Select Code copy to clipboard

Please Login or Register  to view this content.

[Kevin#]

I cannot edit the above post - all the text disappears

This corrects post14:

LookUp Formulas
Formula used to find the correct photo (in cell D6):

Please Login or Register  to view this content.

Formula to look-up the product specification (in cell G2, and copied down):

Please Login or Register  to view this content.

This above lookup searches for the concatenation of cell C2 and cell F2, and looks for match in column A of Sheet “Spec & Photos” (see below***)

[MdYas]

Dear Kevin,

Thanks for your help and i appreciate for the same.

i cant find the attached file.

can you attached the amended file again.

thanks in advance.

[MdYas]

Hi, Kevin

Awaiting for your reply

[Kevin#]

here you go

[Kevin#]

The file was too big - I deleted a couple of photos

[MdYas]

Dear Kevin,

its a pleasure to see your message and i'm lucky to learning from a excel master like you, i appreciate the help and support you have showered on me.

Yaseen