Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup, and Return Cell Address
I have a follow-up question for T Valko.
Can you offer some kind of modification, or alternative, to the second function (below) that you gave me yesterday? In the form that you gave all functions to me, all functions worked great! However, I made some modifications, and now I have a problem here. I used your first function, and ended up counting the number of matches, between items in my Column A on the active sheet, and items in Column A on the Import Sheet: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) CSE, of course. This function is in my Column B of the active sheet, and this works fine. Then, I used your second function, and found all cell addresses on the Import Sheet, which is what I wanted: =IF(ROWS(C2:C$2)<=B2,"A"&SMALL(IF(ISNUMBER(SEARCH( A2,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"") This function is in my Column C. Finally, I used your third function: =INDIRECT("'Import Sheet'!"&E2) This function is in my Column D. I then inserted rows in Column A, sort of as place holders, for each of the numbers which are in Column B of the active sheet. This allowed me to show each of the cell addresses (Column C of my active sheet) and each bit of text from these cell addresses (Column D of my active sheet). So, I thought everything was going to be roses, but then I realized the second function stops counting when I inserted those rows in my Column A! Do you have any idea what could be causing this? Does anyone have an alternative to look up cell addresses, such as Address & Vlookup? Tried a few combinations of things and nothing seemed to work¦¦¦¦. Cordially, Ryan--- -- RyGuy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup, and Return Cell Address
I'm having a hard time trying to "visualize" where you're inserting rows.
A couple of observations: Some of the references in the formula need to be absolute: =IF(ROWS(C2:C$2)<=B2,"A"&SMALL(IF(ISNUMBER(SEARCH( A2,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"") B2 should be B$2 A2 should be A$2 Also, depending on where you're inserting rows will change these expressions: =IF(ROWS(C2:C$2) ROWS(C2:C$2) If these expressions evaluate to be greater than the number returned by this formula: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) Then the formula returns a blank. Is that what you mean by stopped counting? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I have a follow-up question for T Valko. Can you offer some kind of modification, or alternative, to the second function (below) that you gave me yesterday? In the form that you gave all functions to me, all functions worked great! However, I made some modifications, and now I have a problem here. I used your first function, and ended up counting the number of matches, between items in my Column A on the active sheet, and items in Column A on the Import Sheet: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) CSE, of course. This function is in my Column B of the active sheet, and this works fine. Then, I used your second function, and found all cell addresses on the Import Sheet, which is what I wanted: =IF(ROWS(C2:C$2)<=B2,"A"&SMALL(IF(ISNUMBER(SEARCH( A2,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"") This function is in my Column C. Finally, I used your third function: =INDIRECT("'Import Sheet'!"&E2) This function is in my Column D. I then inserted rows in Column A, sort of as place holders, for each of the numbers which are in Column B of the active sheet. This allowed me to show each of the cell addresses (Column C of my active sheet) and each bit of text from these cell addresses (Column D of my active sheet). So, I thought everything was going to be roses, but then I realized the second function stops counting when I inserted those rows in my Column A! Do you have any idea what could be causing this? Does anyone have an alternative to look up cell addresses, such as Address & Vlookup? Tried a few combinations of things and nothing seemed to work..... Cordially, Ryan--- -- RyGuy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup, and Return Cell Address
As I am looking at your second function closer now, I am appreciating it much
more. Maybe one of the conditions is not necessary. I know you are telling me to concatenate the "A" and the k-th smallest value in the data set. This is pretty much what I am after. I think after I get down 30 rows, which is the number returned by your first function, the Count-Search function, the second part of the Small-IsNumber-Search function is being evaluated, and this is basically: If k € 0 or if k exceeds the number of data points, SMALL returns the #NUM! error value (from the MS Help tool). After I go down 30 rows, I get all #NUM! Alternatively, according to the Help tool, the array could be empty, and thus SMALL is returning the #NUM! error value. Do you know of any way around this? The array of the first value sought has ended after 30 rows, but that I want to find the array of the second, value, the third, etc. Regards, Ryan--- -- RyGuy "T. Valko" wrote: I'm having a hard time trying to "visualize" where you're inserting rows. A couple of observations: Some of the references in the formula need to be absolute: =IF(ROWS(C2:C$2)<=B2,"A"&SMALL(IF(ISNUMBER(SEARCH( A2,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"") B2 should be B$2 A2 should be A$2 Also, depending on where you're inserting rows will change these expressions: =IF(ROWS(C2:C$2) ROWS(C2:C$2) If these expressions evaluate to be greater than the number returned by this formula: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) Then the formula returns a blank. Is that what you mean by stopped counting? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I have a follow-up question for T Valko. Can you offer some kind of modification, or alternative, to the second function (below) that you gave me yesterday? In the form that you gave all functions to me, all functions worked great! However, I made some modifications, and now I have a problem here. I used your first function, and ended up counting the number of matches, between items in my Column A on the active sheet, and items in Column A on the Import Sheet: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) CSE, of course. This function is in my Column B of the active sheet, and this works fine. Then, I used your second function, and found all cell addresses on the Import Sheet, which is what I wanted: =IF(ROWS(C2:C$2)<=B2,"A"&SMALL(IF(ISNUMBER(SEARCH( A2,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"") This function is in my Column C. Finally, I used your third function: =INDIRECT("'Import Sheet'!"&E2) This function is in my Column D. I then inserted rows in Column A, sort of as place holders, for each of the numbers which are in Column B of the active sheet. This allowed me to show each of the cell addresses (Column C of my active sheet) and each bit of text from these cell addresses (Column D of my active sheet). So, I thought everything was going to be roses, but then I realized the second function stops counting when I inserted those rows in my Column A! Do you have any idea what could be causing this? Does anyone have an alternative to look up cell addresses, such as Address & Vlookup? Tried a few combinations of things and nothing seemed to work..... Cordially, Ryan--- -- RyGuy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup, and Return Cell Address
I think you are right, the formula returns a blank because the expressions
evaluate to be greater than the number returned by this formula: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) There are a total of 30 After 30 rows, which is the number that results from the Count-Search function, I get #NUM! Is there a workaround? -- RyGuy "T. Valko" wrote: I'm having a hard time trying to "visualize" where you're inserting rows. A couple of observations: Some of the references in the formula need to be absolute: =IF(ROWS(C2:C$2)<=B2,"A"&SMALL(IF(ISNUMBER(SEARCH( A2,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"") B2 should be B$2 A2 should be A$2 Also, depending on where you're inserting rows will change these expressions: =IF(ROWS(C2:C$2) ROWS(C2:C$2) If these expressions evaluate to be greater than the number returned by this formula: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) Then the formula returns a blank. Is that what you mean by stopped counting? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I have a follow-up question for T Valko. Can you offer some kind of modification, or alternative, to the second function (below) that you gave me yesterday? In the form that you gave all functions to me, all functions worked great! However, I made some modifications, and now I have a problem here. I used your first function, and ended up counting the number of matches, between items in my Column A on the active sheet, and items in Column A on the Import Sheet: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) CSE, of course. This function is in my Column B of the active sheet, and this works fine. Then, I used your second function, and found all cell addresses on the Import Sheet, which is what I wanted: =IF(ROWS(C2:C$2)<=B2,"A"&SMALL(IF(ISNUMBER(SEARCH( A2,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"") This function is in my Column C. Finally, I used your third function: =INDIRECT("'Import Sheet'!"&E2) This function is in my Column D. I then inserted rows in Column A, sort of as place holders, for each of the numbers which are in Column B of the active sheet. This allowed me to show each of the cell addresses (Column C of my active sheet) and each bit of text from these cell addresses (Column D of my active sheet). So, I thought everything was going to be roses, but then I realized the second function stops counting when I inserted those rows in my Column A! Do you have any idea what could be causing this? Does anyone have an alternative to look up cell addresses, such as Address & Vlookup? Tried a few combinations of things and nothing seemed to work..... Cordially, Ryan--- -- RyGuy |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup, and Return Cell Address
There are a total of 30 After 30 rows, which is the number that results
from the Count-Search function, I get #NUM! Is there a workaround? That's what this portion of the formula is for: (B2 should be absolute as I noted in my other reply: B$2) =IF(ROWS(C2:C$2)<=B2 If B2 holds this formula and the result is 30: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) This expression: =IF(ROWS(C2:C$2)<=B2 Compares the number of rows the formula is copied to against the value in B2. If the number of rows is greater than B2 then the formula returns a blank. This is used as a form of error trap that is shorter and more efficient than trapping errors when they occur from the SMALL function. If there are 30 items that meet the criteria then SMALL(array,31) returns #NUM!. The above expression is more efficient to use to trap these expected errors. -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I think you are right, the formula returns a blank because the expressions evaluate to be greater than the number returned by this formula: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) There are a total of 30 After 30 rows, which is the number that results from the Count-Search function, I get #NUM! Is there a workaround? -- RyGuy "T. Valko" wrote: I'm having a hard time trying to "visualize" where you're inserting rows. A couple of observations: Some of the references in the formula need to be absolute: =IF(ROWS(C2:C$2)<=B2,"A"&SMALL(IF(ISNUMBER(SEARCH( A2,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"") B2 should be B$2 A2 should be A$2 Also, depending on where you're inserting rows will change these expressions: =IF(ROWS(C2:C$2) ROWS(C2:C$2) If these expressions evaluate to be greater than the number returned by this formula: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) Then the formula returns a blank. Is that what you mean by stopped counting? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I have a follow-up question for T Valko. Can you offer some kind of modification, or alternative, to the second function (below) that you gave me yesterday? In the form that you gave all functions to me, all functions worked great! However, I made some modifications, and now I have a problem here. I used your first function, and ended up counting the number of matches, between items in my Column A on the active sheet, and items in Column A on the Import Sheet: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) CSE, of course. This function is in my Column B of the active sheet, and this works fine. Then, I used your second function, and found all cell addresses on the Import Sheet, which is what I wanted: =IF(ROWS(C2:C$2)<=B2,"A"&SMALL(IF(ISNUMBER(SEARCH( A2,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"") This function is in my Column C. Finally, I used your third function: =INDIRECT("'Import Sheet'!"&E2) This function is in my Column D. I then inserted rows in Column A, sort of as place holders, for each of the numbers which are in Column B of the active sheet. This allowed me to show each of the cell addresses (Column C of my active sheet) and each bit of text from these cell addresses (Column D of my active sheet). So, I thought everything was going to be roses, but then I realized the second function stops counting when I inserted those rows in my Column A! Do you have any idea what could be causing this? Does anyone have an alternative to look up cell addresses, such as Address & Vlookup? Tried a few combinations of things and nothing seemed to work..... Cordially, Ryan--- -- RyGuy |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup, and Return Cell Address
Thanks for the follow-up Biff. Ya know, I thought I understood the logic, so
I popped a count function into B1: =COUNT(B2:B65536) This gives me a count of the used cells in Row B. I changed the search criteria a little, so now there are 13 rows (I had over 43 before). There are four different values in Column A, and the first one is repeated 8 times. This is confirmed with your function: =COUNT(SEARCH($A2,'Import Sheet'!$A$1:$A$65000)) (CSE) This is fine. The second value is found once, the third one is found twice, and the fourth one is found twice. Again, there are a total of 13 values (4 unique) in Column A and 13 values (4 unique) in Column B. I changed your second function to the following: =IF(ROWS(C2:C$2)<=B$1,"A"&SMALL(IF(ISNUMBER(SEARCH (A2,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"") (CSE) Now, since Cell B1 has the value 13 in it, I would think the second function would give me every cell address on the Import sheet that match the values on the active sheet, but the second function stops at row 8 (it doesnt go down 13 rows). Is this enough information to understand what is happening? I would really like to get this straightened out before the weekend. Im going to fuss around with it a little more. If you see something I dont see, please post back. Thanks so much, Ryan--- -- RyGuy "T. Valko" wrote: There are a total of 30 After 30 rows, which is the number that results from the Count-Search function, I get #NUM! Is there a workaround? That's what this portion of the formula is for: (B2 should be absolute as I noted in my other reply: B$2) =IF(ROWS(C2:C$2)<=B2 If B2 holds this formula and the result is 30: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) This expression: =IF(ROWS(C2:C$2)<=B2 Compares the number of rows the formula is copied to against the value in B2. If the number of rows is greater than B2 then the formula returns a blank. This is used as a form of error trap that is shorter and more efficient than trapping errors when they occur from the SMALL function. If there are 30 items that meet the criteria then SMALL(array,31) returns #NUM!. The above expression is more efficient to use to trap these expected errors. -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I think you are right, the formula returns a blank because the expressions evaluate to be greater than the number returned by this formula: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) There are a total of 30 After 30 rows, which is the number that results from the Count-Search function, I get #NUM! Is there a workaround? -- RyGuy "T. Valko" wrote: I'm having a hard time trying to "visualize" where you're inserting rows. A couple of observations: Some of the references in the formula need to be absolute: =IF(ROWS(C2:C$2)<=B2,"A"&SMALL(IF(ISNUMBER(SEARCH( A2,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"") B2 should be B$2 A2 should be A$2 Also, depending on where you're inserting rows will change these expressions: =IF(ROWS(C2:C$2) ROWS(C2:C$2) If these expressions evaluate to be greater than the number returned by this formula: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) Then the formula returns a blank. Is that what you mean by stopped counting? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I have a follow-up question for T Valko. Can you offer some kind of modification, or alternative, to the second function (below) that you gave me yesterday? In the form that you gave all functions to me, all functions worked great! However, I made some modifications, and now I have a problem here. I used your first function, and ended up counting the number of matches, between items in my Column A on the active sheet, and items in Column A on the Import Sheet: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) CSE, of course. This function is in my Column B of the active sheet, and this works fine. Then, I used your second function, and found all cell addresses on the Import Sheet, which is what I wanted: =IF(ROWS(C2:C$2)<=B2,"A"&SMALL(IF(ISNUMBER(SEARCH( A2,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"") This function is in my Column C. Finally, I used your third function: =INDIRECT("'Import Sheet'!"&E2) This function is in my Column D. I then inserted rows in Column A, sort of as place holders, for each of the numbers which are in Column B of the active sheet. This allowed me to show each of the cell addresses (Column C of my active sheet) and each bit of text from these cell addresses (Column D of my active sheet). So, I thought everything was going to be roses, but then I realized the second function stops counting when I inserted those rows in my Column A! Do you have any idea what could be causing this? Does anyone have an alternative to look up cell addresses, such as Address & Vlookup? Tried a few combinations of things and nothing seemed to work..... Cordially, Ryan--- -- RyGuy |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup, and Return Cell Address
At this point I'd need to see file to actually see what you're trying to do.
From my vantage point I can't tell why the formula is not working. Here's what I've been able to figure out from your posts... You're searching a column of data to see if any entries contain some value. You're getting the addresses of any cells that contain that value. Then you're using another formula to return that value. This could be done in a single step instead of 2 steps but like I said above, I'd need to see what you're doing. -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Thanks for the follow-up Biff. Ya know, I thought I understood the logic, so I popped a count function into B1: =COUNT(B2:B65536) This gives me a count of the used cells in Row B. I changed the search criteria a little, so now there are 13 rows (I had over 43 before). There are four different values in Column A, and the first one is repeated 8 times. This is confirmed with your function: =COUNT(SEARCH($A2,'Import Sheet'!$A$1:$A$65000)) (CSE) This is fine. The second value is found once, the third one is found twice, and the fourth one is found twice. Again, there are a total of 13 values (4 unique) in Column A and 13 values (4 unique) in Column B. I changed your second function to the following: =IF(ROWS(C2:C$2)<=B$1,"A"&SMALL(IF(ISNUMBER(SEARCH (A2,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"") (CSE) Now, since Cell B1 has the value 13 in it, I would think the second function would give me every cell address on the Import sheet that match the values on the active sheet, but the second function stops at row 8 (it doesn't go down 13 rows). Is this enough information to understand what is happening? I would really like to get this straightened out before the weekend. I'm going to fuss around with it a little more. If you see something I don't see, please post back. Thanks so much, Ryan--- -- RyGuy "T. Valko" wrote: There are a total of 30 After 30 rows, which is the number that results from the Count-Search function, I get #NUM! Is there a workaround? That's what this portion of the formula is for: (B2 should be absolute as I noted in my other reply: B$2) =IF(ROWS(C2:C$2)<=B2 If B2 holds this formula and the result is 30: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) This expression: =IF(ROWS(C2:C$2)<=B2 Compares the number of rows the formula is copied to against the value in B2. If the number of rows is greater than B2 then the formula returns a blank. This is used as a form of error trap that is shorter and more efficient than trapping errors when they occur from the SMALL function. If there are 30 items that meet the criteria then SMALL(array,31) returns #NUM!. The above expression is more efficient to use to trap these expected errors. -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I think you are right, the formula returns a blank because the expressions evaluate to be greater than the number returned by this formula: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) There are a total of 30 After 30 rows, which is the number that results from the Count-Search function, I get #NUM! Is there a workaround? -- RyGuy "T. Valko" wrote: I'm having a hard time trying to "visualize" where you're inserting rows. A couple of observations: Some of the references in the formula need to be absolute: =IF(ROWS(C2:C$2)<=B2,"A"&SMALL(IF(ISNUMBER(SEARCH( A2,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"") B2 should be B$2 A2 should be A$2 Also, depending on where you're inserting rows will change these expressions: =IF(ROWS(C2:C$2) ROWS(C2:C$2) If these expressions evaluate to be greater than the number returned by this formula: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) Then the formula returns a blank. Is that what you mean by stopped counting? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I have a follow-up question for T Valko. Can you offer some kind of modification, or alternative, to the second function (below) that you gave me yesterday? In the form that you gave all functions to me, all functions worked great! However, I made some modifications, and now I have a problem here. I used your first function, and ended up counting the number of matches, between items in my Column A on the active sheet, and items in Column A on the Import Sheet: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) CSE, of course. This function is in my Column B of the active sheet, and this works fine. Then, I used your second function, and found all cell addresses on the Import Sheet, which is what I wanted: =IF(ROWS(C2:C$2)<=B2,"A"&SMALL(IF(ISNUMBER(SEARCH( A2,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"") This function is in my Column C. Finally, I used your third function: =INDIRECT("'Import Sheet'!"&E2) This function is in my Column D. I then inserted rows in Column A, sort of as place holders, for each of the numbers which are in Column B of the active sheet. This allowed me to show each of the cell addresses (Column C of my active sheet) and each bit of text from these cell addresses (Column D of my active sheet). So, I thought everything was going to be roses, but then I realized the second function stops counting when I inserted those rows in my Column A! Do you have any idea what could be causing this? Does anyone have an alternative to look up cell addresses, such as Address & Vlookup? Tried a few combinations of things and nothing seemed to work..... Cordially, Ryan--- -- RyGuy |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup, and Return Cell Address
Please send me an email and I will send you the file.
Regards, Ryan-- ryguy7272xxxhotmail.com (not sure if the servers will prevent email addresses from being sent; replace the xxx with @) "T. Valko" wrote: At this point I'd need to see file to actually see what you're trying to do. From my vantage point I can't tell why the formula is not working. Here's what I've been able to figure out from your posts... You're searching a column of data to see if any entries contain some value. You're getting the addresses of any cells that contain that value. Then you're using another formula to return that value. This could be done in a single step instead of 2 steps but like I said above, I'd need to see what you're doing. -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Thanks for the follow-up Biff. Ya know, I thought I understood the logic, so I popped a count function into B1: =COUNT(B2:B65536) This gives me a count of the used cells in Row B. I changed the search criteria a little, so now there are 13 rows (I had over 43 before). There are four different values in Column A, and the first one is repeated 8 times. This is confirmed with your function: =COUNT(SEARCH($A2,'Import Sheet'!$A$1:$A$65000)) (CSE) This is fine. The second value is found once, the third one is found twice, and the fourth one is found twice. Again, there are a total of 13 values (4 unique) in Column A and 13 values (4 unique) in Column B. I changed your second function to the following: =IF(ROWS(C2:C$2)<=B$1,"A"&SMALL(IF(ISNUMBER(SEARCH (A2,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"") (CSE) Now, since Cell B1 has the value 13 in it, I would think the second function would give me every cell address on the Import sheet that match the values on the active sheet, but the second function stops at row 8 (it doesn't go down 13 rows). Is this enough information to understand what is happening? I would really like to get this straightened out before the weekend. I'm going to fuss around with it a little more. If you see something I don't see, please post back. Thanks so much, Ryan--- -- RyGuy "T. Valko" wrote: There are a total of 30 After 30 rows, which is the number that results from the Count-Search function, I get #NUM! Is there a workaround? That's what this portion of the formula is for: (B2 should be absolute as I noted in my other reply: B$2) =IF(ROWS(C2:C$2)<=B2 If B2 holds this formula and the result is 30: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) This expression: =IF(ROWS(C2:C$2)<=B2 Compares the number of rows the formula is copied to against the value in B2. If the number of rows is greater than B2 then the formula returns a blank. This is used as a form of error trap that is shorter and more efficient than trapping errors when they occur from the SMALL function. If there are 30 items that meet the criteria then SMALL(array,31) returns #NUM!. The above expression is more efficient to use to trap these expected errors. -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I think you are right, the formula returns a blank because the expressions evaluate to be greater than the number returned by this formula: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) There are a total of 30 After 30 rows, which is the number that results from the Count-Search function, I get #NUM! Is there a workaround? -- RyGuy "T. Valko" wrote: I'm having a hard time trying to "visualize" where you're inserting rows. A couple of observations: Some of the references in the formula need to be absolute: =IF(ROWS(C2:C$2)<=B2,"A"&SMALL(IF(ISNUMBER(SEARCH( A2,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"") B2 should be B$2 A2 should be A$2 Also, depending on where you're inserting rows will change these expressions: =IF(ROWS(C2:C$2) ROWS(C2:C$2) If these expressions evaluate to be greater than the number returned by this formula: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) Then the formula returns a blank. Is that what you mean by stopped counting? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I have a follow-up question for T Valko. Can you offer some kind of modification, or alternative, to the second function (below) that you gave me yesterday? In the form that you gave all functions to me, all functions worked great! However, I made some modifications, and now I have a problem here. I used your first function, and ended up counting the number of matches, between items in my Column A on the active sheet, and items in Column A on the Import Sheet: =COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000)) CSE, of course. This function is in my Column B of the active sheet, and this works fine. Then, I used your second function, and found all cell addresses on the Import Sheet, which is what I wanted: =IF(ROWS(C2:C$2)<=B2,"A"&SMALL(IF(ISNUMBER(SEARCH( A2,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(C2:C$2)),"") This function is in my Column C. Finally, I used your third function: =INDIRECT("'Import Sheet'!"&E2) This function is in my Column D. I then inserted rows in Column A, sort of as place holders, for each of the numbers which are in Column B of the active sheet. This allowed me to show each of the cell addresses (Column C of my active sheet) and each bit of text from these cell addresses (Column D of my active sheet). So, I thought everything was going to be roses, but then I realized the second function stops counting when I inserted those rows in my Column A! Do you have any idea what could be causing this? Does anyone have an alternative to look up cell addresses, such as Address & Vlookup? Tried a few combinations of things and nothing seemed to work..... Cordially, Ryan--- -- RyGuy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
USING VLOOKUP TO RETURN A CELL ADDRESS | Excel Worksheet Functions | |||
LOOKUP & RETURN CELL ADDRESS | Excel Worksheet Functions | |||
V Lookup and return cell address | Excel Worksheet Functions | |||
How do I return the cell address of the largest of a set of values | Excel Discussion (Misc queries) | |||
How do I use a function to return the address of a cell? | Excel Worksheet Functions |