Results 1 to 6 of 6

Thread: Determine Value

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Determine Value

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Excel 2000

    Ok...I promise this is the last one....

    Example

    A2=1650 M2=6/25 N2=3 (the count between dates) 02=??
    A3=1655 M3=""
    A4=1701 M4=""
    A5=1711 M5=6/26 N5=1 O5=(the last value in "A" before the new date is entered, in this case "1701")

    How can this be determined...Again all suggestions are appreciated.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Determine Value

    Roberta,

    In your example the formula should work. Just enter it in Column O, for the extent of your data.<pre> =IF(M5="","",A4)</pre>


    Andrew

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine Value

    Andrew,

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>So Sorry my fault I didn't make it clearer..(that part of it I have)..I need O2 (??) to show the value of "1701"

    Thanks again for your help
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Determine Value

    Riberta,

    Sorry for the delay in getting a response, and for picking you up wrong last time.

    The following Function might do the trick :

    Function ValueForDate(rng As Range, oSet) As Long
    Application.Volatile
    Dim rngAddr As String
    rngAddr = rng.Address
    ValueForDate = Range(rngAddr).offset(oSet - 1, 0).Value
    End Function

    Use it as follows - In O2 enter <pre> =IF(M2="","",ValueForDate(A2,N2))</pre>

    assuming that you entered the previous function (or Brooke's formula) in N2 and Column A is the column you want the value from.

    Andrew

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine Value

    Andrew,

    OMG....U R so GOOD!!!!!! TY TY TY....

    Just wish I knew what u did...lol.

    I am not sure I understand what the coding is saying...but it works GREAT....

    Again TY
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Determine Value

    Andrew,
    This is the solution I was referring to.
    Jeff

Posting Permissions

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