Using date, link to a cell

[Weasel]

I'm working on a workbook with two sheets. One sheet has all my monthl
data (actuals) in it broken up by each day and the other sheet (mai
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 actual
sheet that has the same date.

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


Thanks in advance for any hel

 

[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 th
same size.


HTH

Stev

 

[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 wor
without an issue. What trouble specifically are you having? Do yo
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 mai
flash worksheet:


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

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

Regards,

Stev

 

[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
have a break and instead of a date in the B column of sheet "actuals"
have the word Total, which is the total for the week. so it goes:


Code
-------------------

Column B

3/14
3/15
3/16
3/17
3/18
3/19
3/20
Total
3/21
3/22
3/23
3/24
3/25
3/26
3/27
Total

-------------------


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