Extract Data with dependent dropdown from multiple tables

okay, I've uploaded a dummy workbook.

My initial response is to suggest you have one table on a single sheet rather than multiple sheets which makes any formulae more complex : even more so, if you simply want to extract data for a particular engine type.

You have 2 tables onn a sheet which I assume represent the specific engine type but there is no data which identifies them as such.

I don't have 365 so i wont offer an "old" solution as 365 has many new functions which make data extract easier.

Hello kvsrinivasamurthy,

unfortunately i cant upload the real file, because of sensitive data. Why there are two tables:

In my orginal file, i work with something else than engines (just made that up, to show as an example).

Engine A, all engines in this category are Diesel engines.All the engines within this category are built similar but it may be that the first engine in this category has components which another one doesnt have(comparing A033_Diesel with A055_Diesel). For that reason i made a table for every single product. Engine A sheet: table 1 represents: A033_Diesel and table 2 on this sheet represents: A055_Diesel. (Giving a name to each table, by selecting it and typing name in the cell on the top left)

Some Bills of Materials have 40 rows with information where others only have 20. Creating one big table, would probably make this easier.

I would need a formula that looks for the value of the dependent cell (example) -> 1.Dropdown Engine A --> 2.Dropdown A033_Diesel

and after selecting A033_Diesel it should return the whole table (which is table 1 from Sheet Engine A in this example)

Vlookup would search for A033_Diesel, but it wouldn't return the whole table, because vlookup only returns the value of one cell.

If i search for an electric engine i dont want components listed with 0 that arent in this product. Do you know what i mean?

Im probably thinking to difficult to solve this problem.

thank you guys for your help. Very much appreciated!!
Have a nice weekend and stay safe!

Best Regards

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.