test for date in column question

[Buster]

I am trying to accomplish two things.
1) I want to test for the existance of a value in a range of number that
varies from day to day.

2) I want to return the cell reference of the data obtained in question (1)

Any IDeas? I though of using something akin to H2:INDEX(H:H,COUNT(H:H)+1))
but I think I'm not on target here

Jeff

 

[Max]

Perhaps this example might help move you along a little?

Assume you have
in Sheet1, in A1:A5
---------------------------
11
22
33
44
55

In Sheet2
-------------
Assume you want to sum Sheet1 col A's numbers
from cell A1 until a cell in col A which number
matches a number input in A1 (in Sheet2)

Put in A1, say: 33

This number 33 matches the number in cell A3 in Sheet1
and what you want is: =SUM(Sheet1!A1:A3)
(i.e. 11+22+33 = 66)

If the number in A1 is 44,
what you want is: =SUM(Sheet1!A1:A4)
(i.e. 11+22+33+44 = 110)

And so on

Should there be no matching number in Sheet1's col A,
if Sheet2's A1 contains say: 35 (not found in Sheet1's col A),
let's assume we want blanks: "" to be returned

Try this to get the equivalent of the above ..

Put in B1:

=IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"",SUM(INDIRECT("'Sheet1'!A1:A"&MATCH(A1,Sh
eet1!A:A,0))))