• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Lookups from multiple tables

T

Tboelke

New Member
So I am working on a quoting sheet and I have gotten to the point that I cannot seem to get any of my formulas to work properly. I am trying to calculate price and to be able to do so it will be determined by several factors in the quote sheet. First, I need it to find the appropriate product # from the 3 Slide option tables (on left side of database sheet) and return the cost value, then I need it to find and include the appropriate mounting kit cost value, and then it needs to test against the yes/no options for clips and if yes, include in that cost. Finally I need it all to multiply by the quantity entered. Attached is a sample that should include all relevant info. Any help anyone might have would be greatly appreciated.
 

Attachments

This is terrible set up for Lookup operation. I'd recommend putting all tables into single table.

Also, can you upload sample with expected result, without links to another workbook? Or upload source for links as well.
 
So I have attached the full workbook. So this table setup worked great for every other operation I had, and it also works well for the other fields in the slide section I have, so would you be suggesting just creating another table that includes all three tables and use that just for this operation? Only issue I might see with that is it will be another spot to have to update when we do annual pricing updates and could potentially cause issues. So when looking at the full workbook my question is about the price cells in the slide option section on the quote tab, and all of the info is under slides towards the bottom of the product database tab (and yes, I do know this looks a little nightmarish right now, am planning to work that out once I get this running)....thanks again.
 

Attachments

What version of Excel do you have?

If you have PowerQuery, no need to do manual update. Just refresh and it will auto update from source table.

MS Query can do the same, but you will need VBA to update connection string when workbook location changes etc.
 
Chihiro said:
What version of Excel do you have?

If you have PowerQuery, no need to do manual update. Just refresh and it will auto update from source table.

MS Query can do the same, but you will need VBA to update connection string when workbook location changes etc.
Click to expand...


Working with Office 2010
 
Ok so PowerQuery is definitely an option. Since it's free add-in for 2010.

I'm going into meeting now. But will review it later.
 
Can you clarify which table contains Mounting Kit cost?

I'm guessing "Bracket" table, but it's not clear which kit corresponds to which bracket.
 
Chihiro said:
Can you clarify which table contains Mounting Kit cost?

I'm guessing "Bracket" table, but it's not clear which kit corresponds to which bracket.
Click to expand...


You are correct, I do see what you are saying....when you look in the drop down on the quote page for mounting kits the product number is given at the end of the description which will match the products in the table. If need be that could be rearranged to where the product number comes first etc. for lookup purposes. Not sure if that makes it easier.
 
See attached. Used PowerQuery to merge, add row/column & transform table found in "Product Database". Merged/transformed tables are placed in "Merged" sheet.

For ease of maintenance, I'd strongly suggest that you clean texts (trim extra spaces etc) in "Product Database" tables.

Fair bit of transformation is done in PowerQuery right now. If you have questions let me know.
 

Attachments

Hey that's awesome, thank you so much. I was so focused on trying to use a index/match or if/vlookup function combo that I completely spaced the whole sumif function. Need to study that one a little more. I did a little research on power query, do you know of a good place to learn how to utilize that well?
 
You must log in or register to reply here.
Back
Top