Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with formula
Is there any way to do this.
I have a column of dates. The dates are at regular weekly intervals starting from 1990 until now and they are always a Friday. In a separate worksheet I would like a formula in a cell to return the row number of the first date that appears for 1990. Another cell will have the row number of the first date that appears for 1991 and so on for all years up to now. Could anybody suggest an appropriate formula. Thank you in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with formula
Ben,
On each row of Sheet1 with your dates put the row number. The =ROW() function will do nicely. For the rest of this I'll assume that your dates are in Sheet1 column A and the row numbers are in column B. On Sheet2 in column A create a list of years, 1990 through 2005. In column B, create the first day of each year along the lines of this formula =DATE(A2,1,1), copying the formula down alongside your list of years. Then in Sheet2 column C use the VLOOKUP function to lookup the row numbers from Sheet1. It should look something like this. =VLOOKUP(B2,'Sheet1'A:B,2,TRUE) "Ben" wrote: Is there any way to do this. I have a column of dates. The dates are at regular weekly intervals starting from 1990 until now and they are always a Friday. In a separate worksheet I would like a formula in a cell to return the row number of the first date that appears for 1990. Another cell will have the row number of the first date that appears for 1991 and so on for all years up to now. Could anybody suggest an appropriate formula. Thank you in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with formula
That's perfect. Thanks for your help "Ben" wrote in message ... Is there any way to do this. I have a column of dates. The dates are at regular weekly intervals starting from 1990 until now and they are always a Friday. In a separate worksheet I would like a formula in a cell to return the row number of the first date that appears for 1990. Another cell will have the row number of the first date that appears for 1991 and so on for all years up to now. Could anybody suggest an appropriate formula. Thank you in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula? | Excel Worksheet Functions | |||
Formula works in some cells, doesn't in other | New Users to Excel | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Problem with VBA returning the contents of a long formula. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |