Using date, link to a cell
 

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


--
Weasel
------------------------------------------------------------------------
Weasel's Profile: http://www.excelforum.com/member.php...o&userid=27206
View this thread: http://www.excelforum.com/showthread...hreadid=524879

Using date, link to a cell
 

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=524879

Using date, link to a cell
 

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


--
Weasel
------------------------------------------------------------------------
Weasel's Profile: http://www.excelforum.com/member.php...o&userid=27206
View this thread: http://www.excelforum.com/showthread...hreadid=524879

Using date, link to a cell
 

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=524879

Using date, link to a cell
 

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:


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 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


--
Weasel
------------------------------------------------------------------------
Weasel's Profile: http://www.excelforum.com/member.php...o&userid=27206
View this thread: http://www.excelforum.com/showthread...hreadid=524879