How to populate various cells with data from a table based on selection of a dropdown list

[elsanchez]

Hello,

I am new here and I'm hoping one or some of you might be able to help me with this challenging task I have been given.

I have been charged with creating a sheet for work, it's function is to provide accurate costings for a job but also create a list of parts in that job based on what items are selected to be used. I will try to give an example below.

1. On sheet 1 I use drop down lists to select certain items.
2. Once selected, a cell to the right populates a price.
3. On another sheet, a part number, description and quantity are populated.
4. As I progress through sheet 1, it adds more costs and also populates the parts list so I know what items I need to do the job.
5. On another sheet is a massive table with all the parts, SKU, description, quantity needed, quantity in stock and so on.

I believe I need to use LOOKUP, VLOOKUP and SUMIFS to complete this but it is a little over my head.

Any help or guidance is appreciated.

[Tsjallie]

Hi Elsanchez,
welcome to the forum.
Can you upload your workbook and indicate where you're having problems and what you would like to see there. No formulas needed. Some rules on the required lookups/calculations are welcome.

[elsanchez]

Hello,

I have added comments for each area in yellow. Here is the link to the workbook https://1drv.ms/x/s!AjZulBFvf1W8bUnN0woRzPFBRpM

Let me know if I'm being too vague.

Thanks

[Tsjallie]

I'm getting some picture, but still quite some questions.
I noted them on the sheets. They're in Project Details, Equipment Selection and Procurement. Look for the blue boxes.

[elsanchez]

Sorry where are you referring to? You need to upload your version of the book I think?

[Tsjallie]

Oops
Didn't notice that the upload was refused because of the size of the workbook (1.15MB!).
Recreated it and now it's 98.5KB

[Tsjallie]

To make a start, this is a formula how to construct (part of) the model like in cell B2 on sheet Equipment Selection.

Please Login or Register  to view this content.

[Tsjallie]

I'd recommend that you put the datavalidation records into tables.
As you organized it now adding new data to them (like you did in Pump_Make) doesn't automatically extend the record set. Meaning to you will also have to adapt the named range definition.
Using tables will do that automatically for you so you can always be sure that the whole record set is used.

[Tsjallie]

Here's a version of the workbook with the formula for building the mode string (see post #7)