As I understand it, your daily yield values throughout the year will be in column T on one worksheet.
I have used Sheet2 for this example. If you have given it a different name, you will have to replace Sheet2 in the formula with the name.
Assuming that the yield for 01 January is in cell T2 (I left row 1 for a column heading),
then you can use the VLOOKUP() OR INDIRECT() functions along with some date calculations to identify the cell in column T that contains today's yield.
On Sheet1, your summary sheet, enter this formula in the cell that will show today's yield:
=INDIRECT("'Sheet2'!T" & TEXT(TODAY()-40179+2,"#0") )
The function TODAY() returns a number which represents today in Excel's date system (1 was 01 January 1900 and 40179 was 01 January 2010)
TODAY()-40179 returns the difference in days between today and the first of January.
As your data starts in row 2, and because we only got the difference in dates we have to add an extra 2. If the yield data started on row 3 you would make the adjustment value 3.
The TEXT() function takes the numerical output of the days calculation and converts it to text using a formatting string. These are the same as those used in custom formatting cells. The "#0" means that 9 will become 9 and 10 will become 10. If you used "00" 9 would become 09.
Putting everything together inside the INDIRECT() function you get something like this:
INDIRECT returns the value in cell T38 on Sheet2.
As a result INDIRECT() will return the value from a different cell in column T on Sheet2, each day.
I went through this to show you how the previously posted formula worked, except that in the previous one it also created the name of the worksheet such as 2010 January and used an alternative cell addressing system where cell B1 is R1C2. Hence the FALSE at the end of the INDIRECT() function, making it use RC type addresses, which made it easier to get a column - using numbers rather than letters.
OK, so as you have all the data for the year on one sheet with dates in column T, you can just use VLOOKUP() to return the yield for Today.
On Sheet1 use this formula:
VLOOKUP() will look for today's date in column C, rows 2 to 366 on Sheet2, and return the value from the cell on the same row as the matching date, but 17 columns to it's right.
A value of 1 would return the date found (not much use), i.e., 1 is column C in this case. 18 is column T.
If you wanted to be able to get the result for different days, not just today, use this formula where cell D20 contains a date.
To make this work enter =Today() in cell E20
and in cell E21 enter =E20-1, and drag this down to row E26
You will now have a list of dates for the last seven days (format these cells and D20 in a suitable date format)
Click on cell D20 and select Data Validation ( I don't have Excel 2007 on this PC, so I can't guide you through the Ribbon). When you get the data validation dialog box open, select List from the 'Allow:' drop-down.
In the 'Source:' box use the selection button at the end of it, to select cells E20 to E26. Click OK
Now goto cell D20 and it will have a drop-down list starting with today's date and the dates for the last week.
Select a date and the VLOOKUP() function will return the yield value from column T, on Sheet2 for that date.
Hope this helps.
PS not a pain at all - the forum is here to help provide answers.