Dynamic Data Validation and VLookup from Different Sheets

[Katec]

I am creating a quote program for my work. The main page will be the specifications of the job, the second page will be all the formulas for calculating the costs based on those specification and various look ups. What I would like to do is have all of our materials and their associated sq in costs in a dynamic table on the 4th page that would look something like below.[TABLE="width: 500"]
<tbody>[TR]
[TD]Material 1[/TD]
[TD].009[/TD]
[/TR]
[TR]
[TD]Material 2[/TD]
[TD].008[/TD]
[/TR]
[TR]
[TD]Material 3[/TD]
[TD].007[/TD]
[/TR]
</tbody>[/TABLE]

I would like this table to be dynamic so that new materials can be added as they occur without having to redefine the controls of the dropdown.

Then on the first page, I want to have a drop-down list that will pull from column 1 of the table so you can choose the material. Next to that, I want a VLookup that will pull in the cost of the material. So for instance, if Material 1 is chosen, the cell directly next to the drop-down will pull in .009 so it can be used for the calculations on Tab 2.

It all works great until I add a record to the table. Once I do that, the data validation doesn't see the new record.

I currently have a quote program set up but it uses a combo box button that isn't dynamic.

I am using Excel for Mac ver 16.13.

I hope I have explained everything clearly and thank you in advance for any assistance.

 

[C Moore]

Are you using a list for the data validation source? You can create a named range that is dynamic with OFFSET, say
MatList: =Offset(Cellwith1st mat., 0 [rows], 0 [columns], CountA(Matlist column) - header rows [height])
Source: =INDIRECT("MatList")

Stuff in [brackets] is more to help you with what it means, it doesn't belong in the formula. This creates a dynamic list for materials. You could then use the next column in another name
SizeList: =Offset(Cellwith1st mat., 0 [rows], 1 [columns], CountA(Matlist column) - header rows [height])
Source: =INDIRECT("SizeList")

Hopefully, enough to get you going.

 

[Katec]

If I'm understanding correctly, instead of defining the data validation by the drop down "list", I would go to custom and enter the Offset formula that references my named range? I believe that will work for what I need to do as I had it working perfectly until I tried to add a new record to the list! Thank you so much for your help!!!

 

[C Moore]

Two step process.

In Name Manager make a new name
Name: MatList
Refers to: =OFFSET(Cellwith1st mat., 0 [rows], 0 [columns], CountA(Matlist column) - header rows [height])

In data validation
Allow: List
Source: =INDIRECT("NamedRange")

Its still a list, but the named range is now 'dynamic'.

 

[tonyyy]

Katec,

Welcome to the Board.

If the material list is in an actual Table rather than a range, another approach can be found in the Contextures tutorial - Excel Drop Down Lists. You'll still create a Named Range, but now it references a Table rather than a formula.

Not saying one is better than the other; just offering another option.

Cheers,

tonyyy