Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Referencing Columns By Date
Hi, I'm trying to get a formula to reference a column for values depending
on the corresponding date headers. My layout is below In A2 is TODAY() In J4 thru AJ4 is =IF(J8<"",I4+1,"") In I4 is 11/1/2009 In E7 is =J7/J28 In E8 is =J8/J28 In E9 is =J9/J28 Etc... What I'd like to do is to have the formula look at A2 and find the corresponding date in J4 Thru AJ4 so that it'll only use the numbers in a given column that are derrived from today only. As you can tell my formula is simple but I'm beginging to think what I am looking for is a tad more. Thanks in Advance, Rob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Referencing Columns By Date
I am not sure I understood you query correctly.
The below will return the column index =MATCH(A2,J4:AJ4,0) Using the below two formulas you can refer to the 3rd row of the array J4:AJ100 which is row number 6 =HLOOKUP(A2,$J$4:$AJ$100,3) =INDEX($J4:$AJ$100,3,MATCH($A$2,$J$4:$AJ$4,0)) If this post helps click Yes --------------- Jacob Skaria "Rob" wrote: Hi, I'm trying to get a formula to reference a column for values depending on the corresponding date headers. My layout is below In A2 is TODAY() In J4 thru AJ4 is =IF(J8<"",I4+1,"") In I4 is 11/1/2009 In E7 is =J7/J28 In E8 is =J8/J28 In E9 is =J9/J28 Etc... What I'd like to do is to have the formula look at A2 and find the corresponding date in J4 Thru AJ4 so that it'll only use the numbers in a given column that are derrived from today only. As you can tell my formula is simple but I'm beginging to think what I am looking for is a tad more. Thanks in Advance, Rob |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Referencing Columns By Date
This formula works perfectly!
=INDEX($J4:$AJ$100,3,MATCH($A$2,$J$4:$AJ$4,0)) Thanks!!!!!!! "Jacob Skaria" wrote: I am not sure I understood you query correctly. The below will return the column index =MATCH(A2,J4:AJ4,0) Using the below two formulas you can refer to the 3rd row of the array J4:AJ100 which is row number 6 =HLOOKUP(A2,$J$4:$AJ$100,3) =INDEX($J4:$AJ$100,3,MATCH($A$2,$J$4:$AJ$4,0)) If this post helps click Yes --------------- Jacob Skaria "Rob" wrote: Hi, I'm trying to get a formula to reference a column for values depending on the corresponding date headers. My layout is below In A2 is TODAY() In J4 thru AJ4 is =IF(J8<"",I4+1,"") In I4 is 11/1/2009 In E7 is =J7/J28 In E8 is =J8/J28 In E9 is =J9/J28 Etc... What I'd like to do is to have the formula look at A2 and find the corresponding date in J4 Thru AJ4 so that it'll only use the numbers in a given column that are derrived from today only. As you can tell my formula is simple but I'm beginging to think what I am looking for is a tad more. Thanks in Advance, Rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing date so you can use A value or B value in a formula | Excel Discussion (Misc queries) | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
Referencing Columns in a Row. PLEASE HELP!! | Excel Worksheet Functions | |||
excel formula counting date to date in 4 columns | Excel Worksheet Functions |