Results 1 to 13 of 13
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    lookup (?) (2000)

    I have a list of daily raw data going back to 1/1/1965. I would like a formula that tells me the cell reference that is the first day of the 30th year prior to the current year. For example, in column I have listed the dates of the raw data, beginning with 1/1/1965 through today, 11/14/2004. In column B I have the raw numerical data. I would like a formula that "looks" at the dates in column A and tells me the cell reference of 1/1/1974. Any help?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: lookup (?) (2000)

    How about?
    ="A"&MATCH(DATE(YEAR(TODAY())-30,1,1),A:A,0)

    To get the "raw numerical data" corresponding to this date, you could use:

    =VLOOKUP(DATE(YEAR(TODAY())-30,1,1),A:B,2,0)

    Steve

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

    Re: lookup (?) (2000)

    Thanks, Steve, works swell! One slight twist: what if I have multiple (say 6 columns) of data, and want to get, say, the raw numerical data from column E that corresponds to the same date as per your first formula?

    Thanks again.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: lookup (?) (2000)

    Change the range to read all 6 columns, then grab the fifth one (col E):

    <pre>=VLOOKUP(DATE(YEAR(TODAY())-30,1,1),A:F,5,0)</pre>


    Steve

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

    Re: lookup (?) (2000)

    Yep-works great. Thanks Steve.

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

    Re: lookup (?) (2000)

    Just got another twist to include (very frustrating-why can't they give me the whole problem at first!):Is there a way to use the reference cell derived by your first formula as a reference or an "anchor" in, say, a sum formula. For example, assume your first reference formula returns A3290. In another remote cell on the same or a different worksheet, how can I use that result indirectly in a sum formula, say, that would sum the next 180 cells column B, so that the formula would in essence be "=sum(b3290:b3470)"?

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: lookup (?) (2000)

    <P ID="edit" class=small>(Edited by sdckapr on 15-Nov-04 06:45. Added another formula)</P>How about this:
    =SUM(INDIRECT("B"&MATCH(DATE(YEAR(TODAY())-30,1,1),A:A,0)&":B"&MATCH(DATE(YEAR(TODAY())-30,1,1),A:A,0)+180))

    Here is another that doesn't do 2 lookups:
    =SUM(OFFSET(B1,MATCH(DATE(YEAR(TODAY())-30,1,1),A:A,0)-1,0,180,1))

    Steve

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

    Re: lookup (?) (2000)

    Thanks for your quick response. Unfortunately, when I tried the first formula, I got a zero, and with the second, a zero, but with a circular as well...

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: lookup (?) (2000)

    Where did you put the formula. It worked fine for me in col C1. Are the values in col b, text?

    Is the formula in the range of interest or do any of the values in this range, reference the formula cell? (this would cause the circ reference)

    Steve

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

    Re: lookup (?) (2000)

    Aha-I put the formula on another spreadsheet, but where the reference was to "A:A", I used the cells on the correct worksheet, and, no, all of the cell values are hard data that have been input (i. e., numerical data, not formulas or references).

    I will try putting the formula in c1.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: lookup (?) (2000)

    If you are refereing to things in another sheet, you need to also add the sheet reference to the items.

    Steve

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

    Re: lookup (?) (2000)

    I did.

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: lookup (?) (2000)

    Could you explain how you have it setup, what you want, and what you modified the formulas to?

    The indirect formula will also need the explicit sheet name in it if it is different than the sheet with the formula

    Steve

Posting Permissions

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