0

I have a sheet with multiple drop down lists which select a certain text value. When I reference to these cells in another sheet, I get the number corresponding with the chosen value but not the value itself, ex. I chose a name in my dropdown list, it is the 2nd value in that list so I get "2" as a result in the other sheet when I reference this cell.

I've tried the Value function but to no avail. How can I display the actual value in this new sheet instead of retrieving the number of the value in the list?

Thank you for your time.

Witloofdc
  • 81
  • 12

1 Answers1

1

I'm assuming your using a form combo box control - you've entered an input range and a cell link in the control properties.

Use an INDEX formula to return the item. For example, if your Input range is $B$1:$B$4 and your cell link is $B$11 this formula will return the value: =INDEX($B$1:$B$4,$B$11)

If your link cell is showing 3 the formula is saying 'return the third item from the input range list'.

To return the value through VBA code add this code to a normal module:

Sub Dropdown_OnChange()

    Dim dd As DropDown

    Set dd = ActiveSheet.Shapes(Application.Caller).OLEFormat.Object

    MsgBox dd.List(dd.ListIndex)

End Sub

Right-click your control, select 'Assign Macro' and select the code macro above. The code will work for all drop-down boxes - Application.Caller returns which drop-down fired the event.
NB: Code copied from Return the selected text from a dropdown box

Community
  • 1
  • 1
Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
  • Hi, so I've got the following syntax: =INDEX('sheet1'!$I$4;'sheet1'!$I$9;'sheet1'!D13) to get the value in sheet2, but I get a #VALUE! error. The input range of my box in sheet1 is $I$4:$I$9, box is in sheet 1 cell D13. – Witloofdc Dec 22 '15 at 14:09
  • Just to make sure we're on the same tracks - Sheet1 range I4:I9 contains the values in your combo box. Sheet1 range D13 displays the result when you select an item. So... if I4:I9 contains A, B, C .... F and you select the third item (C) then the number 3 is display in D13? So if you enter this formula in Sheet1 range D14 `=INDEX($I$4:$I$9,$D$13)` it will display 'C'. – Darren Bartrup-Cook Dec 22 '15 at 14:20
  • I've noticed you've used semi-colons in your formula =INDEX('sheet1'!$I$4 ; 'sheet1'!$I$9 ; 'sheet1'!D13) Is this correct in your locality as my formula uses a colon and a comma - the second reference doesn't use the sheet name either. Should it be `=INDEX(Sheet1!$I$4:$I$9,Sheet1!D13)` – Darren Bartrup-Cook Dec 22 '15 at 14:23
  • Yes, "Sheet1 range I4:I9 contains the values in your combo box. Sheet1 range D13 displays the result when you select an item. So... if I4:I9 contains A, B, C .... F and you select the third item (C) then the number 3 is display in D13" is correct, your last syntax comment is giving me a value of the dropdown list, but the first value, not the chosen one, any idea as to why this is? – Witloofdc Dec 22 '15 at 14:31
  • It's definitely I4:I9 in the one that's not working? I initially read your question wrong and put it as I1:I9 - in that case selecting the fourth item in the drop-down would display the first item in your list (that's all I can think that would make it incorrect). – Darren Bartrup-Cook Dec 22 '15 at 14:41
  • yes it's I4:I9 in the first sheet that gives the options for the drop down list. Still retrieving the first value of the list, hmmm.. Is there a way I can do this in VBA Code ? – Witloofdc Dec 22 '15 at 15:04
  • You can get it through VBA code - http://stackoverflow.com/questions/9578038/return-the-selected-text-from-a-dropdown-box I'll add code to my answer. – Darren Bartrup-Cook Dec 22 '15 at 15:18