Using date, link to a cell

[Weasel]

I'm working on a workbook with two sheets. One sheet has all my monthly data (actuals) in it broken up by each day and the other sheet (main flash) pulls data from the actuals sheet. What I want to do is:

1. Enter a date in the main flash sheet
2. Based on that date I need cell B5 to equal data from the actuals sheet that has the same date.

For example: In the actuals sheet I have 3/24/06 listed in cell B22 (with dates above and below in order). In cell C22 I have a value of 500. When I go to the main flash sheet and enter 3/24/06 into Cell A1 I want Cell B5 in the main flash sheet to equal 500. If I enter 3/25/06 I'd want it to look in cell C23 and so on.


Thanks in advance for any help

[SteveG]

SUMPRODUCT would work. In B5 of the main flash sheet.

=SUMPRODUCT((Actuals!B1:B40=MainFlash!A1)*Actuals!C1:C40)

The ranges that SUMPRODUCT is evaluating in Actuals! need to be the same size.


HTH

Steve

[Weasel]

Thanks but I'm having a little trouble with the formula. I put 3/20/06 in cell A1. In sheet "actuals" I have 3/4/06 through 3/31/06 starting in cell B4 and ending at B34. I then have values in cell's C4 through C34 that match up with each day.

When I enter the date in A1 of sheet "main flash" I want the value that's next to the corresponding date in sheet "actuals" to appear in cell B5. So for example if I type 3/20/06 in and in the "actuals" sheet the value (found in cells C4:C34) for that date (dates in B4:B34) is 500, 500 will then appear in cell B5 of the "main flash" sheet

[SteveG]

That is how I understood it to be. The formula I gave you should work without an issue. What trouble specifically are you having? Do you get an error message or is it not pulling what you want it to pull? For your last post example use the following formula in B5 of the main flash worksheet:


=SUMPRODUCT((Actuals!B4:B34=MainFlash!A1)*Actuals! C4:C34)

This should work for you. Could you elaborate on the error or problem you encounter if you enter this in cell B5 of the MainFlash! worksheet?

Regards,

Steve

[Weasel]

Someone else told me to use this formula and it worked:

=VLOOKUP(A1,actuals!B4:C34,2)

Maybe you could help me with the next step though. After every 7 days I have a break and instead of a date in the B column of sheet "actuals" I have the word Total, which is the total for the week. so it goes:

Please Login or Register  to view this content.

In Column C it has the values for each day and then in the total row it has the total for the week. Is it possible, and if so how, to enter 3/20/06 in Cell A1 of the "main flash" file and then instead of the formula giving the value for 3/20/06, it instead gave the value listed in the Total column for that week? Again, thanks in advance for all your help. I can't thank you enough