| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Address & Match
I used the following simple function to get the number of items found in a
list that is imported from the web: {=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))} CSE-entered (thanks Max and Harlan) Lets say there were 30 items found in the entire list. Is there a simple way to get the cell address that corresponds to each item found? Does this require a macro? I am using the following now: =ADDRESS(MATCH(A1,'Import Sheet'!$A$1:$A$65000),1) This, however, is not the correct address! Another pair of eyes on this would be great! What am I missing? Thanks, Ryan--- -- RyGuy |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Address & Match
Try
=ADDRESS(MATCH(A1,'Import Sheet'!$A$1:$A$65000,0),1) -- Regards, Peo Sjoblom "ryguy7272" wrote in message ... I used the following simple function to get the number of items found in a list that is imported from the web: {=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))} CSE-entered (thanks Max and Harlan) Let's say there were 30 items found in the entire list. Is there a simple way to get the cell address that corresponds to each item found? Does this require a macro? I am using the following now: =ADDRESS(MATCH(A1,'Import Sheet'!$A$1:$A$65000),1) This, however, is not the correct address! Another pair of eyes on this would be great! What am I missing? Thanks, Ryan--- -- RyGuy |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Address & Match
The range in MATCH() need to be in ascending order unless the third argument
is FALSE. try {=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000,FALSE))} -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "ryguy7272" wrote in message ... I used the following simple function to get the number of items found in a list that is imported from the web: {=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))} CSE-entered (thanks Max and Harlan) Let's say there were 30 items found in the entire list. Is there a simple way to get the cell address that corresponds to each item found? Does this require a macro? I am using the following now: =ADDRESS(MATCH(A1,'Import Sheet'!$A$1:$A$65000),1) This, however, is not the correct address! Another pair of eyes on this would be great! What am I missing? Thanks, Ryan--- -- RyGuy |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Address & Match
You can try this array formula** but it'll be slow.
Assume you have this formula in cell D1: {=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))} Enter this array formula** in E1 and copy down until you get blanks: =IF(ROWS(E$1:E1)<=D$1,"A"&SMALL(IF(ISNUMBER(SEARCH (A$7,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(E$1:E1)),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I used the following simple function to get the number of items found in a list that is imported from the web: {=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))} CSE-entered (thanks Max and Harlan) Let's say there were 30 items found in the entire list. Is there a simple way to get the cell address that corresponds to each item found? Does this require a macro? I am using the following now: =ADDRESS(MATCH(A1,'Import Sheet'!$A$1:$A$65000),1) This, however, is not the correct address! Another pair of eyes on this would be great! What am I missing? Thanks, Ryan--- -- RyGuy |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Address & Match
These functions worked great; thanks to all for your help. I have one more
question, how can I display the contents of a cell on another sheet? I am trying to match the results from the function Biff gave me, which for example may be A2136, A2147, A2158, and so on and so forth. Basically, I am trying to get the contents of A2136, on the Import Sheet, to show up in D1 of my Names Sheet. I tried the following: =OFFSET('Import Sheet'!A1,Names!D1-1,0) This seems to increase my offset, by one for each row, so by the time I am at the end of my list, the cell reference is off by 30! I thought that I could subtract 1 from the Row part of the function, but it doesn't seem to work. Please help. Regards, Ryan-- -- RyGuy "T. Valko" wrote: You can try this array formula** but it'll be slow. Assume you have this formula in cell D1: {=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))} Enter this array formula** in E1 and copy down until you get blanks: =IF(ROWS(E$1:E1)<=D$1,"A"&SMALL(IF(ISNUMBER(SEARCH (A$7,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(E$1:E1)),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I used the following simple function to get the number of items found in a list that is imported from the web: {=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))} CSE-entered (thanks Max and Harlan) Let's say there were 30 items found in the entire list. Is there a simple way to get the cell address that corresponds to each item found? Does this require a macro? I am using the following now: =ADDRESS(MATCH(A1,'Import Sheet'!$A$1:$A$65000),1) This, however, is not the correct address! Another pair of eyes on this would be great! What am I missing? Thanks, Ryan--- -- RyGuy |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Address & Match
Don't see why you're using OFFSET. Each address returned by the formula will
contain the value held in A$7. Are you wanting to offset that address and return a value from a different column? If E2 = A2136 (this is a TEXT string and not a real cell reference) to use that as a reference you'd need to wrap it inside an INDIRECT function: =INDIRECT("'Import Sheet'!"&E2) Which evaluates to: ='Import Sheet'!A2136 -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... These functions worked great; thanks to all for your help. I have one more question, how can I display the contents of a cell on another sheet? I am trying to match the results from the function Biff gave me, which for example may be A2136, A2147, A2158, and so on and so forth. Basically, I am trying to get the contents of A2136, on the Import Sheet, to show up in D1 of my Names Sheet. I tried the following: =OFFSET('Import Sheet'!A1,Names!D1-1,0) This seems to increase my offset, by one for each row, so by the time I am at the end of my list, the cell reference is off by 30! I thought that I could subtract 1 from the Row part of the function, but it doesn't seem to work. Please help. Regards, Ryan-- -- RyGuy "T. Valko" wrote: You can try this array formula** but it'll be slow. Assume you have this formula in cell D1: {=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))} Enter this array formula** in E1 and copy down until you get blanks: =IF(ROWS(E$1:E1)<=D$1,"A"&SMALL(IF(ISNUMBER(SEARCH (A$7,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(E$1:E1)),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I used the following simple function to get the number of items found in a list that is imported from the web: {=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))} CSE-entered (thanks Max and Harlan) Let's say there were 30 items found in the entire list. Is there a simple way to get the cell address that corresponds to each item found? Does this require a macro? I am using the following now: =ADDRESS(MATCH(A1,'Import Sheet'!$A$1:$A$65000),1) This, however, is not the correct address! Another pair of eyes on this would be great! What am I missing? Thanks, Ryan--- -- RyGuy |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Address & Match
That's it!! I've known of that function for a while now, but I have never
actually used it before. Thanks for the help T. Valko, Sandy Mann, and Peo Sjoblom. Cordially, Ryan--- -- RyGuy "T. Valko" wrote: Don't see why you're using OFFSET. Each address returned by the formula will contain the value held in A$7. Are you wanting to offset that address and return a value from a different column? If E2 = A2136 (this is a TEXT string and not a real cell reference) to use that as a reference you'd need to wrap it inside an INDIRECT function: =INDIRECT("'Import Sheet'!"&E2) Which evaluates to: ='Import Sheet'!A2136 -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... These functions worked great; thanks to all for your help. I have one more question, how can I display the contents of a cell on another sheet? I am trying to match the results from the function Biff gave me, which for example may be A2136, A2147, A2158, and so on and so forth. Basically, I am trying to get the contents of A2136, on the Import Sheet, to show up in D1 of my Names Sheet. I tried the following: =OFFSET('Import Sheet'!A1,Names!D1-1,0) This seems to increase my offset, by one for each row, so by the time I am at the end of my list, the cell reference is off by 30! I thought that I could subtract 1 from the Row part of the function, but it doesn't seem to work. Please help. Regards, Ryan-- -- RyGuy "T. Valko" wrote: You can try this array formula** but it'll be slow. Assume you have this formula in cell D1: {=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))} Enter this array formula** in E1 and copy down until you get blanks: =IF(ROWS(E$1:E1)<=D$1,"A"&SMALL(IF(ISNUMBER(SEARCH (A$7,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(E$1:E1)),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I used the following simple function to get the number of items found in a list that is imported from the web: {=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))} CSE-entered (thanks Max and Harlan) Let's say there were 30 items found in the entire list. Is there a simple way to get the cell address that corresponds to each item found? Does this require a macro? I am using the following now: =ADDRESS(MATCH(A1,'Import Sheet'!$A$1:$A$65000),1) This, however, is not the correct address! Another pair of eyes on this would be great! What am I missing? Thanks, Ryan--- -- RyGuy |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Address & Match
You're welcome!
-- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... That's it!! I've known of that function for a while now, but I have never actually used it before. Thanks for the help T. Valko, Sandy Mann, and Peo Sjoblom. Cordially, Ryan--- -- RyGuy "T. Valko" wrote: Don't see why you're using OFFSET. Each address returned by the formula will contain the value held in A$7. Are you wanting to offset that address and return a value from a different column? If E2 = A2136 (this is a TEXT string and not a real cell reference) to use that as a reference you'd need to wrap it inside an INDIRECT function: =INDIRECT("'Import Sheet'!"&E2) Which evaluates to: ='Import Sheet'!A2136 -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... These functions worked great; thanks to all for your help. I have one more question, how can I display the contents of a cell on another sheet? I am trying to match the results from the function Biff gave me, which for example may be A2136, A2147, A2158, and so on and so forth. Basically, I am trying to get the contents of A2136, on the Import Sheet, to show up in D1 of my Names Sheet. I tried the following: =OFFSET('Import Sheet'!A1,Names!D1-1,0) This seems to increase my offset, by one for each row, so by the time I am at the end of my list, the cell reference is off by 30! I thought that I could subtract 1 from the Row part of the function, but it doesn't seem to work. Please help. Regards, Ryan-- -- RyGuy "T. Valko" wrote: You can try this array formula** but it'll be slow. Assume you have this formula in cell D1: {=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))} Enter this array formula** in E1 and copy down until you get blanks: =IF(ROWS(E$1:E1)<=D$1,"A"&SMALL(IF(ISNUMBER(SEARCH (A$7,'Import Sheet'!A$1:A$65000)),ROW('Import Sheet'!A$1:A$65000)),ROWS(E$1:E1)),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I used the following simple function to get the number of items found in a list that is imported from the web: {=COUNT(SEARCH($A7,'Import Sheet'!$A$1:$A$65000))} CSE-entered (thanks Max and Harlan) Let's say there were 30 items found in the entire list. Is there a simple way to get the cell address that corresponds to each item found? Does this require a macro? I am using the following now: =ADDRESS(MATCH(A1,'Import Sheet'!$A$1:$A$65000),1) This, however, is not the correct address! Another pair of eyes on this would be great! What am I missing? Thanks, Ryan--- -- RyGuy |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| use the result of match function as a part of row address | New Users to Excel | |||
| Partial Address match in an array | Excel Discussion (Misc queries) | |||
| Dynamic Range, Data Validation and Address, Match and Offset Funct | Excel Worksheet Functions | |||
| long address list, name-address-city, listed vertically, how do y. | Excel Discussion (Misc queries) | |||
| How do I import Office address book to Outlook Express address bo. | Excel Discussion (Misc queries) | |||
Linear Mode
