Home |
Search |
Today's Posts |
#1
|
|||
|
|||
test for date in column question
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 |
#2
|
|||
|
|||
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)))) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Buster" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what formula do i put for column m = column k minus column l in e. | Excel Discussion (Misc queries) | |||
How do I sort a column of formulas in Excel? | Excel Discussion (Misc queries) | |||
Copying the contents of a column into a chart | Excel Worksheet Functions | |||
How to calculate the data in excel 2002 including only the last 9. | Excel Worksheet Functions | |||
GradeBook | Excel Worksheet Functions |