+ Reply to Thread
Results 1 to 10 of 10

Value based on Date, Time and High or Low user selected from drop down list

  1. #1
    Registered User
    Join Date
    08-04-2012
    Location
    Hollyood, Florida
    MS-Off Ver
    Excel 2003
    Posts
    5

    Value based on Date, Time and High or Low user selected from drop down list

    Row I is a drop down list of Date derived from Row A
    Row J is a drop down list of time derived from Row G
    Row K is a drop down list of High or Low derived from cell C1, D1

    Row L (Valued needed based on Row I, J, K selection)
    Value based on Date, Time and High or Low user selected from drop down list

    Thanks in advance for any help much appreciated :-)
    Johnny
    Attached Files Attached Files

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: Value based on Date, Time and High or Low user selected from drop down list

    hello, this is ugly but i think this will give the result.



    Please Login or Register  to view this content.
    array formula - ctrl + shift+enter not just enter

    thanks.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    08-04-2012
    Location
    Hollyood, Florida
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Value based on Date, Time and High or Low user selected from drop down list

    Hmm I put in that formula and used Ctrl +Shift + Enter doesn't seem to work. I got #NUM! as a result. I don't know if maybe I'm not doing it right ?

    Thanks in Advance
    Any help much appreciated

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,256

    Re: Value based on Date, Time and High or Low user selected from drop down list

    what answer do you want returned? ie what value/from which column?
    and will the user select either high or low or both?

    the approach im taking is to add 2 helper columns after time (G) and using concatenate on date&time&high/low, and then doing vlookup or index/match on that
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    08-04-2012
    Location
    Hollyood, Florida
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Value based on Date, Time and High or Low user selected from drop down list

    I'm looking for the value high if user selects high from the drop down list or low if the user selects low from the drop down list. High or Low corresponding to the Date and time the user choose. As far as VLookup or HLookup from what I read is that the row's have to be in ascending order. Ascending order doesn't quite work for what I'm trying to accomplish. I could be wrong about the ascending order but if I understood correctly that what MS Excel help seems to indicate.

    Thank you Kindly
    Johnny

  6. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Value based on Date, Time and High or Low user selected from drop down list

    With a helper column in H:

    In H3 and down: =A3+TRIM(G3)

    In L3 and down: =IF(COUNTA(I3:K3)=3,INDEX(C:D,MATCH(I3+TRIM(J3),H:H),MATCH(K3,$C$1:$D$1,0)),"")

  7. #7
    Registered User
    Join Date
    08-04-2012
    Location
    Hollyood, Florida
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Value based on Date, Time and High or Low user selected from drop down list

    I've tried imputting tha formula and seems like I'm getting the same value across the board regardless of Date and Time choosen by user. I don't see a upload attachment here to be able to upload the file to show you :-( . I'll do my best to explain. On row L I get $1275.50 all the way down. I'm not sure if I explained well. I'm trying to have Row L populate the high or low (dependant on user's selection) value from the column that corresponds to Date and time and high or low conditions selected by user from drop down list.

    Thank you for your help. Much appreciated
    Johnny

  8. #8
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Value based on Date, Time and High or Low user selected from drop down list

    Here's your file with the formulas in place:
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-04-2012
    Location
    Hollyood, Florida
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Value based on Date, Time and High or Low user selected from drop down list

    Thank you so much it works great. I don't quite understand the syntax but I'll study it better when I have time. But for now I'll use it by copying and pasting it where I need it - it speeds up my work. Really appreciate your help. I don't know how to thank you enough. Now I have to figure out how to put resolved on here.

    Thank You
    Johnny

    ---------- Post added at 11:08 PM ---------- Previous post was at 11:07 PM ----------

    Ok figured out to put it on resolve

    Thank you
    Johnny

  10. #10
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Value based on Date, Time and High or Low user selected from drop down list

    I don't know how to thank you enough.
    You just did. You're welcome. Glad to have helped.

+ 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