Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
Check out INDIRECT: cpearson.com/excel/indirect.htm –  Excellll Apr 20 '13 at 18:18
add comment

1 Answer

up vote 1 down vote accepted

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 D6.

enter image description here

  1. In a fresh column enter the numeric index of the rows, i.e. 1, 2, 3, 4, 5
  2. In the column to the right, enter the row titles
  3. Repeat the above steps for columns

So you'll have something like this:

enter image description here

Next in the Developer tab on the ribbon, click Insert > 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.

  1. Right click the first combo box and click Properties.
  2. In the ColumnCount field, enter 2
  3. In the ColumnWidths field, enter 0
  4. In the LinkedCell field, enter a cell value off screen, for examples sake, Y1
  5. In the ListFillRange field, enter the range that covers the row index and row title columns, using my image above as an example, I would enter M1:N5
  6. Repeat the above five steps for the second combo box for the columns (to continue example, step 4 value can be in Z1
  7. Now we can get the intersection you want with the following formula: =INDEX(B2:D6,Y1,Z1)

Basically, through the LinkedCell value of the combo boxes, the row and column indices are being populated in Y1 and Z1 respectively. From there we use the Index function to get the value at those row and column offsets in the table B2:D6.

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.)

share|improve this answer
    
Excellent. This was exactly what I was looking for. Thanks a ton. –  Dave Apr 20 '13 at 18:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.