Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup? Referencing cells?
Collumn A has a selection of dates
i.e 1/3/2005 2/3/2005 7/3/2005 9/3/2005 Note: They are not consecutive (otherwise this would be too easy!) On a sheet 2, I want to be able to type a date in a cell. Then have a function by which all the cells beneath are populated by the corresponding dates on the first sheet. In other words excel needs to lookup a date in a specified collumn with the result then coming from the row beneath where the original number is. I hope this makes sense! Please help! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup? Referencing cells?
.. In other words excel needs to lookup a date in a specified col with the
result then coming from the row beneath where the original number is. Not very sure how best to interp the above line, but anyway, here's a play to try .. Assume source dates are in Sheet1, in A2 down In Sheet2, the date will be input in A1 Put in A2: =IF(ROW(A1)COUNT(B:B),"",INDEX(Sheet1!A:A,MATCH(S MALL(B:B,ROW(A1)),B:B,0))) Format A2 as date Put in B2: =IF(OR(Sheet1!A2="",$A$1=""),"",IF(Sheet1!A2=$A$1 ,ROW(),"")) (Leave B1 empty) Select A2:B2, fill down to cover the max expected extent of data in Sheet1's col A. The required results* will be returned in A2 down, all neatly bunched at the top. *dates that are more than or equal to the date input in A1 If however, what you're after are dates corresponding (ie equal to) to the date input in A1, then just use instead in B2, and copy down: =IF(OR(Sheet1!A2="",$A$1=""),"",IF(Sheet1!A2=$A$1, ROW(),"")) (no change to formulas in col A) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Karen" wrote: Collumn A has a selection of dates i.e 1/3/2005 2/3/2005 7/3/2005 9/3/2005 Note: They are not consecutive (otherwise this would be too easy!) On a sheet 2, I want to be able to type a date in a cell. Then have a function by which all the cells beneath are populated by the corresponding dates on the first sheet. In other words excel needs to lookup a date in a specified collumn with the result then coming from the row beneath where the original number is. I hope this makes sense! Please help! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup? Referencing cells?
Line:
*dates that are more than or equal to the date input in A1 perhaps reads better as: *dates that are later than or equal to the date input in A1 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup? Referencing cells?
Sooo helpful! Thank You!
"Max" wrote: Line: *dates that are more than or equal to the date input in A1 perhaps reads better as: *dates that are later than or equal to the date input in A1 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup? Referencing cells?
Welcome, Karen !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Karen" wrote: Sooo helpful! Thank You! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CountIF() in Worksheet B while referencing cells in Worksheet A | Excel Worksheet Functions | |||
Same formula referencing same cells returns incorrect results, randomly, when pasted into new worksheet | Excel Worksheet Functions | |||
vlookup on large text in cells | Excel Worksheet Functions | |||
Referencing cells in different worksheets | Excel Worksheet Functions | |||
Conditional formatting on cells with a VLOOKUP formula in them | Excel Discussion (Misc queries) |