Google Sheets – How to Pull Values for Dynamic Data Validation

google sheetsgoogle-sheets-data-validation

This one is far out of my league.
To simplify things:

Sheet1
B4 has a Data validation drop down list Ex. Vancouver, Surrey, Burnaby, etc
B5 has a Data validation drop down list Ex. 500, 1000, 2000, etc

Sheet2
Has Rows named Vancouver, Surrey, Burnaby, etc
Has Columns named 500, 1000, 2000
The cross reference cells between the rows and columns names have numerical values

On Sheet1
I'd like to create a cell (B6) that shows the value of the selection from B4 and B5.
(Ex. B4 you select Surrey and B5 you select 1000. B6 inputs the value from Sheet2 that is from Row Surrey and Column 1000)

FINAL EDIT
Here's my working code. Special thanks to @marikamitsos

Code: =INDEX('Value Reference'!B2:AJ6,MATCH(B4,'Value Reference'!A2:A15,0),MATCH(B5,'Value Reference'!B1:BR1,0))

I did remove the data validation "list of item" and changed it to "List from a range" (which works much better)
Code: 'Value Reference'!A2:A15 and 'Value Reference'!B1:BR1

Here is the link:
https://docs.google.com/spreadsheets/d/1Ca-buLW7i7lt8Lxw3AshDumm3g_HUPmMD2L2ZWeBj2c/edit#gid=0

Best Answer

Your formula is correct.
You just need to use single ' instead of double " quotes.

=INDEX('Value Reference'!B2:AJ6,MATCH(B4,'Value Reference'!A2:A6,0),MATCH(B5,'Value Reference'!B1:AJ1,0))

EDIT

Make Data validation dynamic

You can improve your results even further.
Instead of using List of items for your Data validation, you can use List from a range.

In order to do this, follow these steps:

  1. In your Value Reference sheet

    • Insert a new column on the left of column A
    • In A1 use the formula =(TRANSPOSE(C1:AK1)) to get the Input Job Value

  2. In your Main Sheet sheet for your Data validation use:

    • For cell B4 use List from a range and 'Value Reference'!B2:B7
    • For cell B5 use List from a range and 'Value Reference'!A1:A44

Now in cell B6 your formula becomes: 

=INDEX('Value Reference'!C2:AK6,MATCH(D4,'Value Reference'!B2:B6,0),MATCH(D5,'Value Reference'!C1:AK1,0))

Related Topic