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
See reply in .Misc
Biff
"gloss" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
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$12) ...*
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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> >
>
>
>
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" <[email protected]> wrote in message
news:[email protected]...
> 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$12) ...*
>
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>> >
>> >
>>
>>
>>
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" <[email protected]> wrote in message
> news:[email protected]...
> > 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$12) ...*
> >
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
>
You're welcome. Hope that helped!
Biff
"gloss" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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$12) ...*
>> >
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks