Results 1 to 6 of 6
  1. #1
    spb
    Guest

    Looking up a cell from user input (Excel 2000)

    I would like to be able to return the value of a cell which is selected by
    user input. For example, in a budget sheet that has categories in rows and
    Months across in columns, I would like to have a two cells at the bottom:
    Category: Food
    Month: Dec
    Amount: (returned)

    were the users fills in the category and the month, and the amount is
    returned. Also, is it possible to have a drop-list for category and month
    to simplify the selection process?
    Thanks for the help!!
    scottburke

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Looking up a cell from user input (Excel 2000)

    spb

    You may want to use VBA for that.
    1) have a User Form with two combo boxes, dropdowns, one for Categories and one for months.
    2) the User will pick the category and the month.
    3) the code will grab the entries picked and you will have named ranges on your worksheet.
    4) The Code will figure out the named range based on the user input.

    This will take some time to write, so if you need help do everything you can do and send it as an attached workbook and I'll try to help you along. BTW you have all what you need in the OLH.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Looking up a cell from user input (Excel 2000)

    Does the amount returned come from a standard budget or price table in the WB? If so, use =VLOOKUP().
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Looking up a cell from user input (Excel 2000)

    > the users fills in the category and the month, and the amount is returned

    Check out Tools-Add Ins, Lookup Wizard

    > it possible to have a drop-list for category and month
    to simplify the selection process?
    Yes, use Data-Validation, Allow: List, Source: Jan, Feb,...

    HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: Looking up a cell from user input (Excel 2000)

    See if the attached sheet does what you want.
    Attached Files Attached Files
    Legare Coleman

  6. #6
    Lounger
    Join Date
    Oct 2001
    Location
    Isleworth, Gtr London, England
    Posts
    28
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Looking up a cell from user input (Excel 2000)

    See this attachment. It uses defined names for each category and month, and defined names for the cells containing the choices made by the drop down lists. And finally it uses a defined name formula of
    =EVALUATE(Category&" "&Month) in a name of Result, to calculate the required value.

    To define names for all categories and all months, select the block containing all of the data, including the row containing the month names, and including the column containing the category descriptions, and press Ctrl-Shift-F3 to create the required names ( it will display a prompt for how to create names, with tick-boxes, of which Top Row and Left Column are the boxes that need to be ticked before pressing OK ).
    You define names for the drop down choice cells in a similar way, or use the menu command Insert/Name/Define to create names.

    I think this method is more readable than Legare Coleman's ( post 82496 ), although this method does not recalculate automatically, i.e. after a choice has been made and a figure is returned, if then the actual figure in data changes, the amount shown by the formula "=Result" doesn't update.

    Glenn Bumford
    Attached Files Attached Files

Posting Permissions

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