Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    using the results of controls (2K)

    if I have a control which presents a list based on a range elsewhere in the worksheet and from which the user selects a value, for example a week ending date. How can I refer to that selection in a formula? Taking the week ending data as an example I may want to display other information related to that specific week based on values held in the sheet such as week number and shift pattern. I can lookup the value fine but I don't know how to get hold of the matcching criteria which is the user selection from the control? The reference data may look like that below and the user will have selected from a list based on column A.

    .................A.........................B...... ....C
    .........w/e date..................No....shift pattern
    6.......04/05/2003.............18..........b
    7.......11/05/2003.............19..........b
    8.......18/05/2003.............20..........a
    9.......25/05/2003.............21..........b

    Thank you............ liz

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: using the results of controls (2K)

    If you set the BoundColumn property to "0", then the Control.value property is the ROW of the dataset (zero-based) and you can use this with INDEX.

    The TEXT property is the "item" that the user selected.
    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using the results of controls (2K)

    Set the Linked Cell property of the list box to a cell where you want the selected item from the list stored (this can be a cell that is covered by the list box if you want). Then you can refer to that cell to get the item selected in the list box.

    BTW, it sounds like using Data Validation would be a better way of doing this. Select all of the cells where you want the week ending date, and then select Valadation from the Data Menu. In the Allow drop down list select List, and then enter the range where the week ending dates are stored.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •