+ Reply to Thread
Results 1 to 8 of 8

Formula based on which item is selected in combo box.

  1. #1
    Forum Contributor
    Join Date
    11-09-2007
    Location
    USA
    MS-Off Ver
    2003
    Posts
    141

    Formula based on which item is selected in combo box.

    I attached a sample of what I am trying to do.

    On Sheet 1 I select something in the combo box. I then enter a number into the yellow cell. Based on what it is I selected in the combo box I need to run a certain formula and have the answer fall into Sheet 1 cell F3.

    Example: Select Spyker and enter 1200 into the yellow box, the formula would be =IF(E3>0,E3/(Sheet2!B3*58.75),"") and then Sheet1 F3 would give me the answer of 8.17.

    Everytime I select a different item in the combo box it would have to use the corresponding info.

    Thanks for looking.
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Did you set a linked to Cell behind the combobox so that the actual cell E3 actually populates?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    1:link combo box to cell d3 (go to design mode,right click combo box/properties and type d3 in linked cell) exit design mode
    2. in f3

    =IF(E3>0,E3/(VLOOKUP(D3,Sheet2!A:B,2,FALSE)*58.75),"")

  4. #4
    Forum Contributor
    Join Date
    11-09-2007
    Location
    USA
    MS-Off Ver
    2003
    Posts
    141
    Quote Originally Posted by NBVC View Post
    Did you set a linked to Cell behind the combobox so that the actual cell E3 actually populates?
    E3 is populated by the user, it could vary.

  5. #5
    Forum Contributor
    Join Date
    11-09-2007
    Location
    USA
    MS-Off Ver
    2003
    Posts
    141
    D3 is the connection for the "Spyker". How do I link other items in the combo box if I already pointed the linked cell to d3?

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    it doesnt work like that. whatever you select in the combo box then populates the linked cell
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    11-09-2007
    Location
    USA
    MS-Off Ver
    2003
    Posts
    141
    It works great. I now kinda understand it. I have been altering it to my needs actually.

    Sometimes on sheet2 I may have a blank cell so I can actually select nothing then in the combo box. Is it possible to alter the formula so that when nothing is selected in the combo box, E3 & F3 clear as well?

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    since e3 is manualy entered you cant hav a formula in e3 as well, i supose a vba script could be written to clear e3 if blank is chosen in the combo box

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1