Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
find column letter
I need a formula that can look for a date in
a cell that matchs a date in a range and then enter column letter in the formula cell Formula in A1 Variable cell (date) A2 Range B1:ah6 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
find column letter
In A1 enter:
=col_id(A2,B1:AH6) Where col_id is the following UDF: Function col_id(r As Range, tbl As Range) As String Dim d1 As Date, cel As Range col_id = "" d1 = r.Value For Each cel In tbl If cel.Value = d1 Then col_id = Split(cel.Address, "$")(1) Exit Function End If Next End Function -- Gary''s Student - gsnu200802 "Stuart WJG" wrote: I need a formula that can look for a date in a cell that matchs a date in a range and then enter column letter in the formula cell Formula in A1 Variable cell (date) A2 Range B1:ah6 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
find column letter
Stuart,
To use worksheet functions, you can only check one row at a time: =SUBSTITUTE(SUBSTITUTE(ADDRESS(1,MATCH(A$2,B1:AH1, FALSE)+1),"$1",""),"$","") and then copy down for each row you want to check. HTH, Bernie MS Excel MVP "Stuart WJG" wrote in message ... I need a formula that can look for a date in a cell that matchs a date in a range and then enter column letter in the formula cell Formula in A1 Variable cell (date) A2 Range B1:ah6 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
find column letter
Well, here's part of an answer. This formula will return the column
identifier based on a column number. In the example, the column number is assumed to be in cell E4: =IF(E426,CHAR(CEILING(E4/26,1)+63),"")&CHAR(IF(MOD(E4,26)=0,26,MOD(E4,26))+ 64) The original source for that, I believe, is John Walkenbach. It handles anything up to column number 702 (ZZ) which was fine for Excel 2003 and earlier versions. To give a more complete solution, I need to know more about where your dates are at in that range of B1:AH6 - are they in a particular column (in which case you'd already have the answer) or on a particular row, or is the entire matrix filled with dates? "Stuart WJG" wrote: I need a formula that can look for a date in a cell that matchs a date in a range and then enter column letter in the formula cell Formula in A1 Variable cell (date) A2 Range B1:ah6 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
find column letter
Put this in A1:
=IF(ISNA(MATCH(A2,$B$1:$AH$1,0)),"",IF(MATCH(A2,$B $1:$AH $1,0)25,"A"&CHAR(MATCH(A2,$B$1:$AH$1,0)+39),CHAR( MATCH(A2,$B$1:$AH $1,0)+65))) Hope this helps. Pete On Aug 29, 1:38*pm, Stuart WJG wrote: I need a formula that can look for a date in a cell that matchs a date in a range and then enter column letter in the formula cell Formula in A1 Variable cell (date) A2 Range B1:ah6 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
find column letter
Sorry, I thought your range was B1:AH1 - ignore my post.
Pete On Aug 29, 2:29*pm, Pete_UK wrote: Put this in A1: =IF(ISNA(MATCH(A2,$B$1:$AH$1,0)),"",IF(MATCH(A2,$B $1:$AH $1,0)25,"A"&CHAR(MATCH(A2,$B$1:$AH$1,0)+39),CHAR( MATCH(A2,$B$1:$AH $1,0)+65))) Hope this helps. Pete On Aug 29, 1:38*pm, Stuart WJG wrote: I need a formula that can look for a date in a cell that matchs a date in a range and then enter column letter in the formula cell Formula in A1 Variable cell (date) A2 Range B1:ah6- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
find column letter
Yeah, I was kind of thinking the same thing - or that the date would be on
one of the rows. But realized he has a whole matrix and I didn't know what was in it. Looks like Gary''s Student has given a solution that should deal with that eventuality although I was kind of hoping to stay away from a UDF myself. "Pete_UK" wrote: Sorry, I thought your range was B1:AH1 - ignore my post. Pete On Aug 29, 2:29 pm, Pete_UK wrote: Put this in A1: =IF(ISNA(MATCH(A2,$B$1:$AH$1,0)),"",IF(MATCH(A2,$B $1:$AH $1,0)25,"A"&CHAR(MATCH(A2,$B$1:$AH$1,0)+39),CHAR( MATCH(A2,$B$1:$AH $1,0)+65))) Hope this helps. Pete On Aug 29, 1:38 pm, Stuart WJG wrote: I need a formula that can look for a date in a cell that matchs a date in a range and then enter column letter in the formula cell Formula in A1 Variable cell (date) A2 Range B1:ah6- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
find column letter
Hi
Understand first part but what is UDF ? "Gary''s Student" wrote: In A1 enter: =col_id(A2,B1:AH6) Where col_id is the following UDF: Function col_id(r As Range, tbl As Range) As String Dim d1 As Date, cel As Range col_id = "" d1 = r.Value For Each cel In tbl If cel.Value = d1 Then col_id = Split(cel.Address, "$")(1) Exit Function End If Next End Function -- Gary''s Student - gsnu200802 "Stuart WJG" wrote: I need a formula that can look for a date in a cell that matchs a date in a range and then enter column letter in the formula cell Formula in A1 Variable cell (date) A2 Range B1:ah6 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
find column letter
Hi there
the dates are in the range sya b1 to z1. say the date of 01/08/08 is in B1 in the range. I need to able to enter a date say in A2 and then put a formula in A1 that looks at the date in A2 say 01/08/08 and returns the column later in this case as B in say a3 "JLatham" wrote: Well, here's part of an answer. This formula will return the column identifier based on a column number. In the example, the column number is assumed to be in cell E4: =IF(E426,CHAR(CEILING(E4/26,1)+63),"")&CHAR(IF(MOD(E4,26)=0,26,MOD(E4,26))+ 64) The original source for that, I believe, is John Walkenbach. It handles anything up to column number 702 (ZZ) which was fine for Excel 2003 and earlier versions. To give a more complete solution, I need to know more about where your dates are at in that range of B1:AH6 - are they in a particular column (in which case you'd already have the answer) or on a particular row, or is the entire matrix filled with dates? "Stuart WJG" wrote: I need a formula that can look for a date in a cell that matchs a date in a range and then enter column letter in the formula cell Formula in A1 Variable cell (date) A2 Range B1:ah6 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
find column letter
In that case, Pete_UK has a good solution (the others are also good, I just
read his a little more because he made same assumption I almost did at first) - but change the B1:AH1 in his formula to B1:Z1 to be more exact. Actually his B1:AH1 would probably still work, since you're going to encounter the date (or not) by the time you get to Z anyhow and it'll never have to look beyond Z in that case. "Stuart WJG" wrote: Hi there the dates are in the range sya b1 to z1. say the date of 01/08/08 is in B1 in the range. I need to able to enter a date say in A2 and then put a formula in A1 that looks at the date in A2 say 01/08/08 and returns the column later in this case as B in say a3 "JLatham" wrote: Well, here's part of an answer. This formula will return the column identifier based on a column number. In the example, the column number is assumed to be in cell E4: =IF(E426,CHAR(CEILING(E4/26,1)+63),"")&CHAR(IF(MOD(E4,26)=0,26,MOD(E4,26))+ 64) The original source for that, I believe, is John Walkenbach. It handles anything up to column number 702 (ZZ) which was fine for Excel 2003 and earlier versions. To give a more complete solution, I need to know more about where your dates are at in that range of B1:AH6 - are they in a particular column (in which case you'd already have the answer) or on a particular row, or is the entire matrix filled with dates? "Stuart WJG" wrote: I need a formula that can look for a date in a cell that matchs a date in a range and then enter column letter in the formula cell Formula in A1 Variable cell (date) A2 Range B1:ah6 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
find column letter
See his reply to my question - looks like you gave him good solution anyhow.
"Pete_UK" wrote: Sorry, I thought your range was B1:AH1 - ignore my post. Pete On Aug 29, 2:29 pm, Pete_UK wrote: Put this in A1: =IF(ISNA(MATCH(A2,$B$1:$AH$1,0)),"",IF(MATCH(A2,$B $1:$AH $1,0)25,"A"&CHAR(MATCH(A2,$B$1:$AH$1,0)+39),CHAR( MATCH(A2,$B$1:$AH $1,0)+65))) Hope this helps. Pete On Aug 29, 1:38 pm, Stuart WJG wrote: I need a formula that can look for a date in a cell that matchs a date in a range and then enter column letter in the formula cell Formula in A1 Variable cell (date) A2 Range B1:ah6- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
find column letter
UDFs are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the UDF will be saved with it. To use the UDF from the normal Excel window, just enter it like a normal Excel Function To remove the UDF: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To learn more about UDFs, see: http://www.cpearson.com/excel/Writin...ionsInVBA.aspx -- Gary''s Student - gsnu200802 "Stuart WJG" wrote: Hi Understand first part but what is UDF ? "Gary''s Student" wrote: In A1 enter: =col_id(A2,B1:AH6) Where col_id is the following UDF: Function col_id(r As Range, tbl As Range) As String Dim d1 As Date, cel As Range col_id = "" d1 = r.Value For Each cel In tbl If cel.Value = d1 Then col_id = Split(cel.Address, "$")(1) Exit Function End If Next End Function -- Gary''s Student - gsnu200802 "Stuart WJG" wrote: I need a formula that can look for a date in a cell that matchs a date in a range and then enter column letter in the formula cell Formula in A1 Variable cell (date) A2 Range B1:ah6 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
find column letter
Hi Stuart.
Here's something else to play with, Put this in A3 and drag down to A8 =IF(ISNA(MATCH($A$2,B1:AH1,0)),"",MATCH($A$2,B1:AH 1,0)) Then put this in A1 =ADDRESS(MATCH(MAX(A3:A8),A3:A8,0),MAX(A3:A8)+1,4) It will return the address of the cell where the match with A2 is made, however, will fall down if there is more than one match. If there is going to be more than one match then the same approach will work but it will need some modifications. HTH Martin "Stuart WJG" wrote in message ... I need a formula that can look for a date in a cell that matchs a date in a range and then enter column letter in the formula cell Formula in A1 Variable cell (date) A2 Range B1:ah6 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
find column letter
If your date range only goes up to Z1, so there will only be one
column letter returned, then you can simplify my formula considerably as follows: =IF(ISNA(MATCH(A2,$B$1:$Z$1,0)),"",CHAR(MATCH(A2,$ B$1:$Z$1,0)+65)) Hope this helps. Pete On Aug 29, 3:18*pm, Stuart WJG wrote: Hi there the dates are in the range sya b1 to z1. say the date of 01/08/08 is in B1 in the range. I need to able to enter a date say in A2 and then put a formula in A1 that looks at the date in A2 say 01/08/08 and returns the column later in this case as B in say a3 "JLatham" wrote: Well, here's part of an answer. *This formula will return the column identifier based on a column number. *In the example, the column number is assumed to be in cell E4: =IF(E426,CHAR(CEILING(E4/26,1)+63),"")&CHAR(IF(MOD(E4,26)=0,26,MOD(E4,26))* +64) The original source for that, I believe, is John Walkenbach. *It handles anything up to column number 702 (ZZ) which was fine for Excel 2003 and earlier versions. To give a more complete solution, I need to know more about where your dates are at in that range of B1:AH6 - are they in a particular column (in which case you'd already have the answer) or on a particular row, or is the entire matrix filled with dates? "Stuart WJG" wrote: I need a formula that can look for a date in a cell that matchs a date in a range and then enter column letter in the formula cell Formula in A1 Variable cell (date) A2 Range B1:ah6- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I turn excel columns from column number to column letter? | Setting up and Configuration of Excel | |||
How to replace column letter in refferences with a function using the old column letter? | Links and Linking in Excel | |||
How to replace column letter in refferences with a function using the old column letter? | Excel Worksheet Functions | |||
column header changed from letter to number, how return to letter | Excel Discussion (Misc queries) | |||
Find column letter containing specific data | Excel Worksheet Functions |