Date controlled formula

[Zarir Medhora]

Hello I would like some help on a excel formula. I have a worksheet with
dates in one row and figures in other and want the totals to be shown @
another area.

Example

Date 01/02/2004 02/02/2004 03/02/2004 04/02/2004 05/02/2004

Amount 10 20 30 40 50




If I Input date here >>> 02/02/2004

Auto amount selected by formula in this cell = 20

& if I change the date to 03/02/2004 the amount would come up as 30



Any help would be appreciated.

Thank you

Zarir
(e-mail address removed)

 

[Niek Otten]

Hi Zarir,

=HLOOKUP(A5,A1:E2,2,FALSE)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[JE McGimpsey]

one way:

Assume your data are in A1:F2, and your date input cell is G1. Then

=HLOOKUP(G1,A1:F2,2,FALSE)

 

[A.W.J. Ales]

Zarir,

Assuming :
Your dates are in cells A1:E1.
Your value are in cells A2:E2

The value on basis of which you want the output ( in your examples :
2-2-2004 or 3-2-2004 is in cell A5 and the outcome you want to be placed
in cell A6 then :

In cell A6 the formule :
=HLOOKUP(A5;A1:E2;2;0)



--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Frank Kabel]

Hi Zarir
if your dates start in A1 and your amounts in A2 try the following:
=SUM(OFFSET($A$2,0,MATCH($A$3,$1:$1,0)-1,1,COUNTA($2:$2)-MATCH($A$3,$1:
$1,0)+1))
A3 stores the input date

HTH
Frank

 

[Frank Kabel]

sorry
after rereading your question I evaluated the sum of all columns >=
your date entry
-> use one of the other suggestions

Frank