The value of a ListBox Item

[TK]

Hi
If for example a Listbox was populate with the following list:
The Item's name in the first col and the items value in the second

A1 B1
Item Sq Ft Per Piece
Brick Pavers .33
Plywood 32
Concrete Per Yard 80
Whatever 1

Then elsewhere in the workbook an item is picked from the listbox as
illustrated below. The area B1*C1 is divided by its value to get the amount
needed.
How, can the value of the item picked (in this case plywood -32-) be written
in the formula in d1?


A1 B1 C1 D1
Item Length Width Amount Needed
Plywood 8 8 =(B1*C1) / "the value of plywood"

The answer is 2 of course.


Thanks

 

[sebastienm]

Hi TK,
Assuming:
- you use a Listbox from the Control Toolbox toolbar and not from the Forms
toolbar.
- the control is on a sheet

1.Display the Properties window.
- select the control
- click the Properties icon on the COntrol Toolbox toolbars, or right-click
and choose Properties

2. Set the listbox
- In the Properties window, set the ColumnCount property to 2 to display the
2 columns A and B
- set the BoundColumn to 2 so that the Value property of the listbox will
return the data from 2nd column (instead of usual default 1st col)
- set thr ListFillRange property to sheet1!A2:B5 to dsplay these vales in
the List
(assuming the list data are in sheet1 range A1:B5)
- set the LinkedCell to a cell where the selected value in the List will be
sent:
sayt to sheet2!A1 if you want the value sent to that address

3. Formula
To return to your formula, since sheet2!A1 contains now the value selected
in the Listbox you can just use:
= (B1*C1) / sheet2!A1

Now if your control is in a Userform, some of these properties' names are
different:
- replace ListFillRange by RowSource
- replace LinkedCell by ControlSource

Regards,
Sebastien

 

[TK]

Sebastienm "Thank you" I haven't had time to work the procedure yet but I
have a hard copy and will work on it tomorrow.
Thanks again: