look for column num

[is1ma1il]

I Have text "G1" in a cell on sheet1, and in sheet two in cell a1="MONTHYEAR" b1="AST" c1="RSG" d1="KET" e1="G1".

I wish to have a formula that will tell me that "G1" is in column 5 for me to put into another formula.

thanks for your help

 

[dwilson01]

I'm not exactly sure what you are trying to do, but this will find whatever you type in the inputbox and print the row and column number to the immediate windows.

Sub findG1()

Dim Response As String

Response = InputBox("Enter search text&quot
If Response <> &quot;&quot; Then
Worksheets(&quot;Sheet2&quot.Activate
Rows(&quot;1:10&quot.Select
With Selection
Set c = .Find(What:=Response, LookIn:=xlValues)
Debug.Print c.Row & &quot; &quot; & c.Column
End With
End If

End Sub

 

[Hasit]

Set up a range for column 5 and call it whatever you wish to as long as it is not related to Excel specific words, such as &quot;E1&quot; etc.

So, instead of searching for E1=&quot;G1&quot;, you can do a [rangename]=&quot;G1&quot;

For example, if your range name was TESTG1 (which referenced all cells in column E), then your test could be TESTG1=&quot;G1&quot;

As for setting up a formula, please elaborate what you wish to do in more detail.

 

[xlhelp]

The formula

=MATCH(&quot;G1&quot;,A1:E1,0)

will tell you that what you are looking for is in the fifth position from the starting point. If your starting point is not Column A then you would have to add a constant to come up with the right number.

HTH

Indu

 

[is1ma1il]

still not working guys

 

[xlhelp]

Are you entering your formula on Sheet1 or Sheet2?

Indu

 

[dwilson01]

Try putting this in sheet 1

=5*MATCH(&quot;G1&quot;,Sheet2!1:1,0)

It will multiply 5 times the column number that G1 is found in. Thanks for the MATCH tip xlhelp, I willmake good use of this.

 

[Hasit]

I am still unclear about what you are trying to do, especially your text: &quot;....for me to put into another formula.&quot;

However, if you want to test whether G1 appears anywhere in column 5 (which is column E), then this is what I did:

I highlighted the whole of column E. The CTRL F3 and named this range &quot;TestG1&quot;. Then using the formula below, I tested for G1 in any cell in the range TestG1.

=IF(TestG1=&quot;G1&quot;,&quot;Match&quot;,&quot;No Match&quot

Whereever G1 appeared, I got a Match....

Is this what you want??