+ Reply to Thread
Results 1 to 4 of 4

Multiply with value-assigned-text in a drop box

  1. #1
    Registered User
    Join Date
    05-26-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    2

    Question Multiply with value-assigned-text in a drop box

    hello all,

    I've been trying to figure this out all afternoon & everytime I think I'm close to a solution, it throws up more barriers...

    I have now assigned values to a number of text cells, and put these in a drop box (via Data Validation). I need the value of the text to multiply by data entered into another column (and which is variable).

    More specific:
    Column C is time spent (entered as .25, .5, .75 or 1).
    Column D is a drop box (repeated in each cell down), where one of 4 choices is possible: Heavy, Medium, Light, Relax and each of these is assigned a value (6, 4, 2, -2). The values for these -for the Data Validation- are in K5:L8.

    What I'm trying to get in Column E: Time spent*Value assigned to "text" selected from the drop box.


    The multiplication does work if I use =C4*heavy or =C4*light so I know I got that part right, but that kinda defeats the whole purpose of what I want to do. I want to select a factor and have it come up with result by itself, without inputting the whole everytime!!


    HELP!?

    Thanks!
    Last edited by EWV; 05-26-2013 at 02:23 PM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Multiply with value-assigned-text in a drop box

    What exactly do you have in K5:L8?

    Is it the text, eg heavy, light etc, in the first column and the values in the second column?

    Also, what's in the data validation? Is it the text values?

    If it is then you can use VLOOKUP to get the value based on what's seleted in data validation.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    05-26-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Multiply with value-assigned-text in a drop box

    Hi Norie,

    Yeah, have text in first & values in second columns. And the text values in the data validation.

    I tried your formula and it works!!! Thank you so much!!! Can I just ask: what is the 2, 0 referring to at the end of it? The rest I get.

    Thanks again, such a huge help (and should have posted hours earlier! I'll know for next time... )

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Multiply with value-assigned-text in a drop box

    Glad it worked, thought I might have misinterpreted your setup.

    The 2 in the formula means return the value from the 2nd column of the lookup range ($K$5:$L$8) where the lookup value (D4) matched the 1st column.

    The 0 tells Excel to look for an exact match.

    It's something like that anyway - not the best at explanations myself.

+ 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