| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search returning ans in terms of Rows & Columns
Hi,
Can excel search which cells my answers belong to? example: A B C D E F 4 2 3 6 7 9 Lets say my ans is "3" it will show "number 3 belong to (C,1)" |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search returning ans in terms of Rows & Columns
If your answer is in A2, then
=ADDRESS(1,MATCH($A$2,A1:F1,0),4,1) gives C1. Regards, Stefi Will ezt *rta: Hi, Can excel search which cells my answers belong to? example: A B C D E F 4 2 3 6 7 9 Lets say my ans is "3" it will show "number 3 belong to (C,1)" |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search returning ans in terms of Rows & Columns
In C,1 format
=SUBSTITUTE(ADDRESS(1,MATCH($A$2,A1:F1,0),2,1),"$" ,",") -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Stefi" wrote in message ... If your answer is in A2, then =ADDRESS(1,MATCH($A$2,A1:F1,0),4,1) gives C1. Regards, Stefi "Will" ezt rta: Hi, Can excel search which cells my answers belong to? example: A B C D E F 4 2 3 6 7 9 Lets say my ans is "3" it will show "number 3 belong to (C,1)" |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search returning ans in terms of Rows & Columns
stefi,
wat if i have a few rolls of number? A B C D E F 1 5 6 7 8 9 10 2 11 12 13 19 20 21 i wan my ans to show; number 8 belong to D,1 I try to use ur formula but I cant get the result "Stefi" wrote: If your answer is in A2, then =ADDRESS(1,MATCH($A$2,A1:F1,0),4,1) gives C1. Regards, Stefi Will ezt *rta: Hi, Can excel search which cells my answers belong to? example: A B C D E F 4 2 3 6 7 9 Lets say my ans is "3" it will show "number 3 belong to (C,1)" |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search returning ans in terms of Rows & Columns
That's another case!
I could solve it only with a UDF: Public Function FindInRng(findrng As Range, ansrng As Range) As String FindInRng = findrng.Find(What:=ansrng.Value, After:=findrng(1), LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Address(False, False) End Function Usage (answer being in G1): =findinrng(A1:F2,$G$1) Adjust A1:F2 range to your needs! Regards, Stefi Will ezt *rta: stefi, wat if i have a few rolls of number? A B C D E F 1 5 6 7 8 9 10 2 11 12 13 19 20 21 i wan my ans to show; number 8 belong to D,1 I try to use ur formula but I cant get the result "Stefi" wrote: If your answer is in A2, then =ADDRESS(1,MATCH($A$2,A1:F1,0),4,1) gives C1. Regards, Stefi Will ezt *rta: Hi, Can excel search which cells my answers belong to? example: A B C D E F 4 2 3 6 7 9 Lets say my ans is "3" it will show "number 3 belong to (C,1)" |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search returning ans in terms of Rows & Columns
If that UDF is gonna be used in a cell on a worksheet, then it won't work until
xl2002. ..Find doesn't work in xl2k and below if used in a function called from a cell on a worksheet. Stefi wrote: That's another case! I could solve it only with a UDF: Public Function FindInRng(findrng As Range, ansrng As Range) As String FindInRng = findrng.Find(What:=ansrng.Value, After:=findrng(1), LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Address(False, False) End Function Usage (answer being in G1): =findinrng(A1:F2,$G$1) Adjust A1:F2 range to your needs! Regards, Stefi Will ezt *rta: stefi, wat if i have a few rolls of number? A B C D E F 1 5 6 7 8 9 10 2 11 12 13 19 20 21 i wan my ans to show; number 8 belong to D,1 I try to use ur formula but I cant get the result "Stefi" wrote: If your answer is in A2, then =ADDRESS(1,MATCH($A$2,A1:F1,0),4,1) gives C1. Regards, Stefi Will ezt *rta: Hi, Can excel search which cells my answers belong to? example: A B C D E F 4 2 3 6 7 9 Lets say my ans is "3" it will show "number 3 belong to (C,1)" -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search returning ans in terms of Rows & Columns
Then it's up to Will either to use XL2003 or find somebody who can solve this
task with worksheet functions! Stefi Dave Peterson ezt *rta: If that UDF is gonna be used in a cell on a worksheet, then it won't work until xl2002. ..Find doesn't work in xl2k and below if used in a function called from a cell on a worksheet. Stefi wrote: That's another case! I could solve it only with a UDF: Public Function FindInRng(findrng As Range, ansrng As Range) As String FindInRng = findrng.Find(What:=ansrng.Value, After:=findrng(1), LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Address(False, False) End Function Usage (answer being in G1): =findinrng(A1:F2,$G$1) Adjust A1:F2 range to your needs! Regards, Stefi âžWillâ ezt Ã*rta: stefi, wat if i have a few rolls of number? A B C D E F 1 5 6 7 8 9 10 2 11 12 13 19 20 21 i wan my ans to show; number 8 belong to D,1 I try to use ur formula but I cant get the result "Stefi" wrote: If your answer is in A2, then =ADDRESS(1,MATCH($A$2,A1:F1,0),4,1) gives C1. Regards, Stefi âžWillâ ezt Ã*rta: Hi, Can excel search which cells my answers belong to? example: A B C D E F 4 2 3 6 7 9 Lets say my ans is "3" it will show "number 3 belong to (C,1)" -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search returning ans in terms of Rows & Columns
Or change the UDF
Public Function FindInRng(findrng As Range, ansrng As Range) As String Dim oRow As Range Dim iPos As Long For Each oRow In findrng.Rows On Error Resume Next iPos = Application.Match(ansrng, oRow.Cells, 0) On Error GoTo 0 If iPos 0 Then FindInRng = Application.Substitute( _ Cells(oRow.Row, iPos).Address(, False), "$", ",") Exit For Else findrng = "" End If Next oRow End Function -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Stefi" wrote in message ... Then it's up to Will either to use XL2003 or find somebody who can solve this task with worksheet functions! Stefi "Dave Peterson" ezt rta: If that UDF is gonna be used in a cell on a worksheet, then it won't work until xl2002. ..Find doesn't work in xl2k and below if used in a function called from a cell on a worksheet. Stefi wrote: That's another case! I could solve it only with a UDF: Public Function FindInRng(findrng As Range, ansrng As Range) As String FindInRng = findrng.Find(What:=ansrng.Value, After:=findrng(1), LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Address(False, False) End Function Usage (answer being in G1): =findinrng(A1:F2,$G$1) Adjust A1:F2 range to your needs! Regards, Stefi ?zWill? ezt *rta: stefi, wat if i have a few rolls of number? A B C D E F 1 5 6 7 8 9 10 2 11 12 13 19 20 21 i wan my ans to show; number 8 belong to D,1 I try to use ur formula but I cant get the result "Stefi" wrote: If your answer is in A2, then =ADDRESS(1,MATCH($A$2,A1:F1,0),4,1) gives C1. Regards, Stefi ?zWill? ezt *rta: Hi, Can excel search which cells my answers belong to? example: A B C D E F 4 2 3 6 7 9 Lets say my ans is "3" it will show "number 3 belong to (C,1)" -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search returning ans in terms of Rows & Columns
Yes, it's a nice solution. The best thing should have been to ask Will what
version does he use! Stefi Bob Phillips ezt *rta: Or change the UDF Public Function FindInRng(findrng As Range, ansrng As Range) As String Dim oRow As Range Dim iPos As Long For Each oRow In findrng.Rows On Error Resume Next iPos = Application.Match(ansrng, oRow.Cells, 0) On Error GoTo 0 If iPos 0 Then FindInRng = Application.Substitute( _ Cells(oRow.Row, iPos).Address(, False), "$", ",") Exit For Else findrng = "" End If Next oRow End Function -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Stefi" wrote in message ... Then it's up to Will either to use XL2003 or find somebody who can solve this task with worksheet functions! Stefi "Dave Peterson" ezt *rta: If that UDF is gonna be used in a cell on a worksheet, then it won't work until xl2002. ..Find doesn't work in xl2k and below if used in a function called from a cell on a worksheet. Stefi wrote: That's another case! I could solve it only with a UDF: Public Function FindInRng(findrng As Range, ansrng As Range) As String FindInRng = findrng.Find(What:=ansrng.Value, After:=findrng(1), LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Address(False, False) End Function Usage (answer being in G1): =findinrng(A1:F2,$G$1) Adjust A1:F2 range to your needs! Regards, Stefi â?zWillâ? ezt Ã*rta: stefi, wat if i have a few rolls of number? A B C D E F 1 5 6 7 8 9 10 2 11 12 13 19 20 21 i wan my ans to show; number 8 belong to D,1 I try to use ur formula but I cant get the result "Stefi" wrote: If your answer is in A2, then =ADDRESS(1,MATCH($A$2,A1:F1,0),4,1) gives C1. Regards, Stefi â?zWillâ? ezt Ã*rta: Hi, Can excel search which cells my answers belong to? example: A B C D E F 4 2 3 6 7 9 Lets say my ans is "3" it will show "number 3 belong to (C,1)" -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search returning ans in terms of Rows & Columns
I am using 2003.
"Stefi" wrote: Yes, it's a nice solution. The best thing should have been to ask Will what version does he use! Stefi Bob Phillips ezt *rta: Or change the UDF Public Function FindInRng(findrng As Range, ansrng As Range) As String Dim oRow As Range Dim iPos As Long For Each oRow In findrng.Rows On Error Resume Next iPos = Application.Match(ansrng, oRow.Cells, 0) On Error GoTo 0 If iPos 0 Then FindInRng = Application.Substitute( _ Cells(oRow.Row, iPos).Address(, False), "$", ",") Exit For Else findrng = "" End If Next oRow End Function -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Stefi" wrote in message ... Then it's up to Will either to use XL2003 or find somebody who can solve this task with worksheet functions! Stefi "Dave Peterson" ezt *rta: If that UDF is gonna be used in a cell on a worksheet, then it won't work until xl2002. ..Find doesn't work in xl2k and below if used in a function called from a cell on a worksheet. Stefi wrote: That's another case! I could solve it only with a UDF: Public Function FindInRng(findrng As Range, ansrng As Range) As String FindInRng = findrng.Find(What:=ansrng.Value, After:=findrng(1), LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Address(False, False) End Function Usage (answer being in G1): =findinrng(A1:F2,$G$1) Adjust A1:F2 range to your needs! Regards, Stefi â?zWillâ? ezt Ã*rta: stefi, wat if i have a few rolls of number? A B C D E F 1 5 6 7 8 9 10 2 11 12 13 19 20 21 i wan my ans to show; number 8 belong to D,1 I try to use ur formula but I cant get the result "Stefi" wrote: If your answer is in A2, then =ADDRESS(1,MATCH($A$2,A1:F1,0),4,1) gives C1. Regards, Stefi â?zWillâ? ezt Ã*rta: Hi, Can excel search which cells my answers belong to? example: A B C D E F 4 2 3 6 7 9 Lets say my ans is "3" it will show "number 3 belong to (C,1)" -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Search returning ans in terms of Rows & Columns
Stefi
The "best" solution is one that works across all versions, not just Will's version. Gord Dibben MS Excel MVP On Wed, 4 Apr 2007 07:10:03 -0700, Stefi wrote: Yes, it's a nice solution. The best thing should have been to ask Will what version does he use! Stefi Bob Phillips ezt rta: Or change the UDF Public Function FindInRng(findrng As Range, ansrng As Range) As String Dim oRow As Range Dim iPos As Long For Each oRow In findrng.Rows On Error Resume Next iPos = Application.Match(ansrng, oRow.Cells, 0) On Error GoTo 0 If iPos 0 Then FindInRng = Application.Substitute( _ Cells(oRow.Row, iPos).Address(, False), "$", ",") Exit For Else findrng = "" End If Next oRow End Function -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Stefi" wrote in message ... Then it's up to Will either to use XL2003 or find somebody who can solve this task with worksheet functions! Stefi "Dave Peterson" ezt rta: If that UDF is gonna be used in a cell on a worksheet, then it won't work until xl2002. ..Find doesn't work in xl2k and below if used in a function called from a cell on a worksheet. Stefi wrote: That's another case! I could solve it only with a UDF: Public Function FindInRng(findrng As Range, ansrng As Range) As String FindInRng = findrng.Find(What:=ansrng.Value, After:=findrng(1), LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Address(False, False) End Function Usage (answer being in G1): =findinrng(A1:F2,$G$1) Adjust A1:F2 range to your needs! Regards, Stefi ?zWill?? ezt *rta: stefi, wat if i have a few rolls of number? A B C D E F 1 5 6 7 8 9 10 2 11 12 13 19 20 21 i wan my ans to show; number 8 belong to D,1 I try to use ur formula but I cant get the result "Stefi" wrote: If your answer is in A2, then =ADDRESS(1,MATCH($A$2,A1:F1,0),4,1) gives C1. Regards, Stefi ?zWill?? ezt *rta: Hi, Can excel search which cells my answers belong to? example: A B C D E F 4 2 3 6 7 9 Lets say my ans is "3" it will show "number 3 belong to (C,1)" -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| comparing columns and returning max value | Excel Discussion (Misc queries) | |||
| Search Columns & Rows | Excel Discussion (Misc queries) | |||
| searching a large database with a long list of search terms | Excel Discussion (Misc queries) | |||
| FIND or SEARCH Returning Erroneous #VALUE? | Excel Worksheet Functions | |||
| how to set up a vlookup table with 2 search terms? | Excel Worksheet Functions | |||
Linear Mode
