Control Listbox & Equation irregularity

[smma]

I have a control listbox in my XL spreadsheet. Its ListFillRange is D3255, and its LinkedCell is L6. The BoundColumn value is set to 0. In cell D6, I have a formula that retrieves the value which corresponds to the row that is selected in the listbox: =INDIRECT("F"&32+L6). This takes the index value returned from the listbox in L6 and retrieves the value of the same row. This is well and good. However, whenever I close out this sheet and reopnen it, the value of the last item picked is cleared out. This is due to the fact that the BoundColumn's value is set to 0. When it is set to 1, it shows the last value picked; however, it also returns the value and not the index number, which screws up the aforementioned formula.

That being said, I could change my formula in D6 to:

=VLOOKUP(L6,D32:F55,3).

But when I do, it always resolved the second argument of the array as 1 and not the correct value of the listbox, giving the addres as 1,3 of the array instead of any other index level of the listbox. Has anyone else encountered this problem, and if so, what is the solution to make this work? BTW, I have already tried using INDEX/MATCH in lieu of this formula, which ends in the same result.

On the other side, I could keep my original configuration if I could get the right VBA code to display the last selection in the listbox, placing the code in the ThisWorkbook, Autpen routine. I know it has something to do with Listbox.Text, but I can't seem to get past the errors that come up that way either.

I know this is a long posting, of which I apologize, but this is a convoluted mess and I can't seem to figure it out by myself. Your help will be appreciated to achieve

Q'Apla! (Success in Klingon)

 

[joerd]

It probably helps if you set the RangeLookup argument of VLOOKUP to False:

= VLOOKUP(L6, D32:F55, 3, FALSE)


Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[smma]

Sorry, Joerd...False gives me #N/A.

 

[joerd]

That means that there is no exact match between what is in L6 and what is in your LOOKUP column (i.e. D3255). Please check.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[smma]

But how is that possible? L6 gets its value from the combobox as the LinkedCell, which gets its values from D3255 as the ListFillRange. How can they not be exact? I even went back and changed the range & L6 to Text format, hoping to make it more specific. Is there any other problem? An options setting, perhaps?

 

[smma]

Okay, Joerd! I got it fixed. The vlookup formula has been changed to =VLOOKUP(VALUE(L6),D3255,3,FALSE). All of the values in the lookup except one are numbers, so they will be force-formatted by the addition of the VALUE() function which gives a proper return. To get the first value, which is just a Tilde (~), my final formula is thus:

=IF(L6="~","~",VLOOKUP(VALUE(L6),D3255,3,FALSE))

Which tells it to return the tilde if that is what L6 has, or do to lookup otherwise. Thanks for helping!

 

[joerd]

Glad to help

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[cummings54]

smma, here's a little out of the box thinking...

if you make the ListFillRange is D32:F55 and set the BoundColumn value to 3 and ColumnCount to 3, a trivial bit of VBA code gets your answer as follows:

Private Sub ListBox1_Click()
  Me.Cells(1, 2) = ListBox1.Value
End Sub