Results 1 to 13 of 13
Thread: lookup (?) (2000)

20041114, 13:07 #1
 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?

20041114, 17:28 #2
 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

20041114, 18:11 #3
 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.

20041114, 20:16 #4
 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

20041114, 20:43 #5
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: lookup (?) (2000)
Yepworks great. Thanks Steve.

20041115, 10:38 #6
 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 frustratingwhy 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)"?

20041115, 10:45 #7
 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 15Nov04 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

20041115, 12:13 #8
 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...

20041115, 12:45 #9
 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

20041115, 12:51 #10
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: lookup (?) (2000)
AhaI 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.

20041115, 13:21 #11
 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

20041115, 13:23 #12
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: lookup (?) (2000)
I did.

20041115, 13:32 #13
 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