| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search using multiple worksheet
Hi
Does anyone have any idea how I can do a search and extract data from a sparate worksheet for example search ("smi*", 'anotherWorksheet.xls'!Users) does not recurse through the column in the second worksheet what am i doing wrong? Any help is much appreciated |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search using multiple worksheet
See reply in .Misc
Biff "gloss" wrote in message ... Hi Does anyone have any idea how I can do a search and extract data from a sparate worksheet for example search ("smi*", 'anotherWorksheet.xls'!Users) does not recurse through the column in the second worksheet what am i doing wrong? Any help is much appreciated |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search using multiple worksheet
Still having the same problem
search("mat*",'anotherExcel.xls'!Sheet1'!$E$12) works but is inefficient for what I want and because I'll be applying this to multiple fields search("mat*",'anotherExcel.xls'!Sheet1'!$E$1:$E$1 2) ...* doesn't work, I initally tried the point method suggested and is exactly the same formula as above in ...* (except that I grouped my data) I'm new to excel, only started today, and could have misunderstood by what you meant by point to the reference (the easier method) could you please elaborate Thank you for your help "Biff" wrote: See reply in .Misc Biff "gloss" wrote in message ... Hi Does anyone have any idea how I can do a search and extract data from a sparate worksheet for example search ("smi*", 'anotherWorksheet.xls'!Users) does not recurse through the column in the second worksheet what am i doing wrong? Any help is much appreciated |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search using multiple worksheet
Hi!
What exactly are you trying to do? You don't need to use a wildcard with SEARCH. SEARCH returns the number of the character at which a specific character or text string is first found. For example: A1 = material......=SEARCH("mat",A1) = 1 A2 = raw material......=SEARCH("mat",A2) = 5 A3 = format......=SEARCH("mat",A3) = 4 A4 = style......=SEARCH("mat",A4) = #VALUE! 'anotherExcel.xls'!Sheet1'!$E$12 If you're referencing another file the correct syntax is: [anotherExcel.xls]Sheet1!$E$12 When I said point to the references what I meant was use your mouse: Start typing the formula: =SEARCH("mat", When you reach the point in the formula to enter the reference use your mouse and navigate to the other file (have the other file open) Sheet1 and select cell E12. This way Excel will automatically insert the path for you.. My guess is that you really don't want to use SEARCH but I need a better explanation of EXACTLY what you're trying to do. Are you wanting to count how many cells contain the substring "mat" ? Biff "gloss" wrote in message ... Still having the same problem search("mat*",'anotherExcel.xls'!Sheet1'!$E$12) works but is inefficient for what I want and because I'll be applying this to multiple fields search("mat*",'anotherExcel.xls'!Sheet1'!$E$1:$E$1 2) ...* doesn't work, I initally tried the point method suggested and is exactly the same formula as above in ...* (except that I grouped my data) I'm new to excel, only started today, and could have misunderstood by what you meant by point to the reference (the easier method) could you please elaborate Thank you for your help "Biff" wrote: See reply in .Misc Biff "gloss" wrote in message ... Hi Does anyone have any idea how I can do a search and extract data from a sparate worksheet for example search ("smi*", 'anotherWorksheet.xls'!Users) does not recurse through the column in the second worksheet what am i doing wrong? Any help is much appreciated |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search using multiple worksheet
Thank You Biff
"Biff" wrote: Hi! What exactly are you trying to do? You don't need to use a wildcard with SEARCH. SEARCH returns the number of the character at which a specific character or text string is first found. For example: A1 = material......=SEARCH("mat",A1) = 1 A2 = raw material......=SEARCH("mat",A2) = 5 A3 = format......=SEARCH("mat",A3) = 4 A4 = style......=SEARCH("mat",A4) = #VALUE! 'anotherExcel.xls'!Sheet1'!$E$12 If you're referencing another file the correct syntax is: [anotherExcel.xls]Sheet1!$E$12 When I said point to the references what I meant was use your mouse: Start typing the formula: =SEARCH("mat", When you reach the point in the formula to enter the reference use your mouse and navigate to the other file (have the other file open) Sheet1 and select cell E12. This way Excel will automatically insert the path for you.. My guess is that you really don't want to use SEARCH but I need a better explanation of EXACTLY what you're trying to do. Are you wanting to count how many cells contain the substring "mat" ? Biff "gloss" wrote in message ... Still having the same problem search("mat*",'anotherExcel.xls'!Sheet1'!$E$12) works but is inefficient for what I want and because I'll be applying this to multiple fields search("mat*",'anotherExcel.xls'!Sheet1'!$E$1:$E$1 2) ...* doesn't work, I initally tried the point method suggested and is exactly the same formula as above in ...* (except that I grouped my data) I'm new to excel, only started today, and could have misunderstood by what you meant by point to the reference (the easier method) could you please elaborate Thank you for your help "Biff" wrote: See reply in .Misc Biff "gloss" wrote in message ... Hi Does anyone have any idea how I can do a search and extract data from a sparate worksheet for example search ("smi*", 'anotherWorksheet.xls'!Users) does not recurse through the column in the second worksheet what am i doing wrong? Any help is much appreciated |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Search using multiple worksheet
You're welcome. Hope that helped!
Biff "gloss" wrote in message ... Thank You Biff "Biff" wrote: Hi! What exactly are you trying to do? You don't need to use a wildcard with SEARCH. SEARCH returns the number of the character at which a specific character or text string is first found. For example: A1 = material......=SEARCH("mat",A1) = 1 A2 = raw material......=SEARCH("mat",A2) = 5 A3 = format......=SEARCH("mat",A3) = 4 A4 = style......=SEARCH("mat",A4) = #VALUE! 'anotherExcel.xls'!Sheet1'!$E$12 If you're referencing another file the correct syntax is: [anotherExcel.xls]Sheet1!$E$12 When I said point to the references what I meant was use your mouse: Start typing the formula: =SEARCH("mat", When you reach the point in the formula to enter the reference use your mouse and navigate to the other file (have the other file open) Sheet1 and select cell E12. This way Excel will automatically insert the path for you.. My guess is that you really don't want to use SEARCH but I need a better explanation of EXACTLY what you're trying to do. Are you wanting to count how many cells contain the substring "mat" ? Biff "gloss" wrote in message ... Still having the same problem search("mat*",'anotherExcel.xls'!Sheet1'!$E$12) works but is inefficient for what I want and because I'll be applying this to multiple fields search("mat*",'anotherExcel.xls'!Sheet1'!$E$1:$E$1 2) ...* doesn't work, I initally tried the point method suggested and is exactly the same formula as above in ...* (except that I grouped my data) I'm new to excel, only started today, and could have misunderstood by what you meant by point to the reference (the easier method) could you please elaborate Thank you for your help "Biff" wrote: See reply in .Misc Biff "gloss" wrote in message ... Hi Does anyone have any idea how I can do a search and extract data from a sparate worksheet for example search ("smi*", 'anotherWorksheet.xls'!Users) does not recurse through the column in the second worksheet what am i doing wrong? Any help is much appreciated |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| combine multiple excel file in to one excel file and multiple worksheet | Excel Discussion (Misc queries) | |||
| combine multiple excel file in to one excel file and multiple worksheet | Excel Discussion (Misc queries) | |||
| How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions | |||
| Search a worksheet, extract rows using a list from another sheet | Excel Discussion (Misc queries) | |||
| Multiple worksheet master | Excel Discussion (Misc queries) | |||
Linear Mode
