I'm creating a spreadsheet for calculating costs of crafting in a game to determine profit ratios. There is a section of my spreadsheet where I would like to have a two drop down lists. In the first one I would like to select the column title of a table, and in the second I'd like to select the row title of a table. In a third cell I would like to use the value in the cell located at this intersection in a formula. I'm sure there has to be a way to do this, but I haven't found anything quite like it with Google. All the drop down list dependency tutorials I've found use different methods that don't suit my situation. Thanks very much.
Ok without resorting to VBA scripting, you'll have to make two small tables, of two columns each, the first column being the index and the second being the row / column title.
So lets assume your original table has 5 rows and 3 columns with simple titles such as Row1, Row2, Row3, Row4, Row5 and Col1, Col2, Col3. And this table starts from
A1, so the actual values start from
B2 and go down to
- In a fresh column enter the numeric index of the rows, i.e. 1, 2, 3, 4, 5
- In the column to the right, enter the row titles
- Repeat the above steps for columns
So you'll have something like this:
Next in the Developer tab on the ribbon, click
Combo Box (ActiveX Control). Make two combo boxes where you want your drop down lists to be.
If you are not in Design Mode, click Design Mode.
- Right click the first combo box and click Properties.
- In the
ColumnCountfield, enter 2
- In the
ColumnWidthsfield, enter 0
- In the
LinkedCellfield, enter a cell value off screen, for examples sake,
- In the
ListFillRangefield, enter the range that covers the row index and row title columns, using my image above as an example, I would enter
- Repeat the above five steps for the second combo box for the columns (to continue example, step 4 value can be in
- Now we can get the intersection you want with the following formula:
Basically, through the
LinkedCell value of the combo boxes, the row and column indices are being populated in
Z1 respectively. From there we use the
Index function to get the value at those row and column offsets in the table
Note: For the rows, you could add a column to the right of the existing row titles and enter the row indices (and hide the column). But for columns, you have to create a new table, because the
ListFillRange field of the combo box does not accept horizontal arrays. (Well it does, but it not for the purpose we need it for.)