Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to return a cell reference as result of a formula
I have two tables of data that have been exported from a database, the 2nd is
a subset of the first. The work request ID code in the 2nd table may appear more than once in the main data table that can be several thousand rows long. Also, in the main table the same ID code may have different entries in the Type field ("requested", "scheduled" or "variable"). Using an ID code in the subset table I want to find the cell reference (or row) of an entry in the main table where the same ID code has "variable" in the type field. I have used COUNTIF to find which ID codes are multiples and have then used =IF(H21,VLOOKUP(A2,ReqNo,4,FALSE)="Variable","x") (H2 is the result of the COUNTIF, A2 the ID Code and ReqNo is a named range with ID Code in col1 and Type field in col 4) to find out if the ID Code has "variable" in the Type field. This returns True if it does and False if not. However it does not tell me where in the main table it occurs. (I then have to use Ctrl+F for every occurrence, or autofilter on the main table). I have used MATCH, but that shows me the row of the first match not the match where Type = "Variable". I have tried to wrap my formula in the ADDRESS function but have not managed to have any success. Is there another function that can do this? Can anyone help? It would be much appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to return a cell reference as result of a formula
Bruce
Match & address will work providing that the data on table 1 is sorted. Assuming that the IDs are in column A and column A in named Data1 =ADDRESS(MATCH($A2,Data1,0),1) where there are more than one entry for the lookup, you can show the first and last entries with: =IF(COUNTIF(Data1,A3)=1,ADDRESS(MATCH(A3,Data1,0), 1),ADDRESS(MATCH(A3,Data1,0),1)&", "&ADDRESS(MATCH(A3,Data1,1),1)) Regards peter "Bruce Williams" wrote: I have two tables of data that have been exported from a database, the 2nd is a subset of the first. The work request ID code in the 2nd table may appear more than once in the main data table that can be several thousand rows long. Also, in the main table the same ID code may have different entries in the Type field ("requested", "scheduled" or "variable"). Using an ID code in the subset table I want to find the cell reference (or row) of an entry in the main table where the same ID code has "variable" in the type field. I have used COUNTIF to find which ID codes are multiples and have then used =IF(H21,VLOOKUP(A2,ReqNo,4,FALSE)="Variable","x") (H2 is the result of the COUNTIF, A2 the ID Code and ReqNo is a named range with ID Code in col1 and Type field in col 4) to find out if the ID Code has "variable" in the Type field. This returns True if it does and False if not. However it does not tell me where in the main table it occurs. (I then have to use Ctrl+F for every occurrence, or autofilter on the main table). I have used MATCH, but that shows me the row of the first match not the match where Type = "Variable". I have tried to wrap my formula in the ADDRESS function but have not managed to have any success. Is there another function that can do this? Can anyone help? It would be much appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to return a cell reference as result of a formula
Hi Peter,
Thanks for that it worked very well. It didn't quite give me what I wanted in that it didn't pick out the "Variable" reference, however that was simply overcome by using a secondary sort key to ensure that the "variable" reference was the first in the list. It got much simpler when the list was sorted, which is pretty obvious now! Many thanks. "Billy Liddel" wrote: Bruce Match & address will work providing that the data on table 1 is sorted. Assuming that the IDs are in column A and column A in named Data1 =ADDRESS(MATCH($A2,Data1,0),1) where there are more than one entry for the lookup, you can show the first and last entries with: =IF(COUNTIF(Data1,A3)=1,ADDRESS(MATCH(A3,Data1,0), 1),ADDRESS(MATCH(A3,Data1,0),1)&", "&ADDRESS(MATCH(A3,Data1,1),1)) Regards peter "Bruce Williams" wrote: I have two tables of data that have been exported from a database, the 2nd is a subset of the first. The work request ID code in the 2nd table may appear more than once in the main data table that can be several thousand rows long. Also, in the main table the same ID code may have different entries in the Type field ("requested", "scheduled" or "variable"). Using an ID code in the subset table I want to find the cell reference (or row) of an entry in the main table where the same ID code has "variable" in the type field. I have used COUNTIF to find which ID codes are multiples and have then used =IF(H21,VLOOKUP(A2,ReqNo,4,FALSE)="Variable","x") (H2 is the result of the COUNTIF, A2 the ID Code and ReqNo is a named range with ID Code in col1 and Type field in col 4) to find out if the ID Code has "variable" in the Type field. This returns True if it does and False if not. However it does not tell me where in the main table it occurs. (I then have to use Ctrl+F for every occurrence, or autofilter on the main table). I have used MATCH, but that shows me the row of the first match not the match where Type = "Variable". I have tried to wrap my formula in the ADDRESS function but have not managed to have any success. Is there another function that can do this? Can anyone help? It would be much appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My formulas return the formula not a result in the cell | Excel Worksheet Functions | |||
Using the result of formula as part of reference to a cell in form | Excel Worksheet Functions | |||
Advanced formula - Return result & Show Cell Reference of result | Excel Worksheet Functions | |||
Enter a formula to return a result from a cell | New Users to Excel | |||
reference the result of a formula in a text formatted cell | Excel Discussion (Misc queries) |