Data Validation and What if Sums

[Mr Wolfe]

Hi Everyone,

Just looking for someone to point me in the right Direction,

I'm not sure of the right question to ask to get the answer i need.

but basically,

I want to look at the data from one table and then based on a criteria, reference available cells that fit that criteria and then populate information into another table.

Spanning 2 work sheets. in the same work book.

I tried my best to show my situation below,

I have tried a Sum if approach, however am failing to understand how to populate the Name in Table 2 and the Price next to it from table 3's Data - Matching the filtering criteria.

Table 1			Table 2					Table 3 (in worksheet 2)
Width	Depth		Design	Cost	Starter	Full		Design/Name	Width	Depth	Cost
9​	24​		Provide filtered list (names - Column A) of (Sheet 2's) table 3's width & depth (column) (row) that is within/below table ones parameters	Provide Tables 3's (as selected at the column to the lefts) Specific Cost to that particular row item.	I can populate this requirement	I can populate this requirement
15​	24​		"For example - In Worksheet 2's table - Row 7,10,23's Column A, shows as a drop down list in this cell because in those rows column c's & d's (width and depth are lower than what is shown in table 1's width and depth column"	"For example - if row 7's column A is selected, then Row 7's Column D (which is the price column - Automatically populates here"
11.5​	27.6​		""	""
9​	24​		""	""
9​	24​		""	""
9​	24​		""	""
14​	24​		""	""
14​	24​		""	""
18.5​	24​		""	""
9​	24​		""	""
14​	24​		""	""
14​	24​		""	""
14​	24​		""	""
19​	24​		""	""

 

[Mr Wolfe]

Im not expecting someone to do the above for me, could you at the very least even point me towards a youtube tutorial with the same type of scenario,

just dont know what it is exactly i need to learn,

thanks in advance!

 

[jasonb75]

The 'Design' dropdown in table 2 will require dynamic validation lists, if such things could be created on the fly by use of array formulas then that would possibly be a way to make it work.
Current versions of excel do not allow this, meaning that dynamic lists have to be created in worksheet tables for validation to refer to as a static range.

This means that you would need to create a table for each possible pair of criteria in table 1 (not just those in the current list, but all possible combinations).

The only alternative would be to populate the dropdowns on the fly as delimited lists using vba to compare W & D in table 1 to those in table 3.

That is assuming that I'm understanding the question correctly.