Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   NZ Computing (http://www.velocityreviews.com/forums/f47-nz-computing.html)
-   -   Excel FIND option request (http://www.velocityreviews.com/forums/t577902-excel-find-option-request.html)

Caught 02-24-2006 10:54 PM

Excel FIND option request
 
Help please .....

I have been given the task of keeping the accounts [on excel] in order. The
business has the sales client name and figures listed per month on different
worksheets.

When a payment comes in for a specific amount, it would be very useful to go
to a function that could search every worksheet. [All I seem to be able to
do is the methodically do a 'find' on every worksheet]

Is there any way in which the FIND can be used to search every worksheet?




Crains 02-24-2006 10:57 PM

Re: Excel FIND option request
 
Caught wrote:
> Help please .....
>
> I have been given the task of keeping the accounts [on excel] in
> order. The business has the sales client name and figures listed per
> month on different worksheets.
>
> When a payment comes in for a specific amount, it would be very
> useful to go to a function that could search every worksheet. [All I
> seem to be able to do is the methodically do a 'find' on every
> worksheet]
> Is there any way in which the FIND can be used to search every
> worksheet?


Which version of Excel are you using? In Excel 2003 if you press Ctrl+F for
find then click on options there is a field called "Within" and u can set
that to "Workbook" instead of "Sheet".



Caught 02-24-2006 11:29 PM

Re: Excel FIND option request
 

"Crains" <none@none.invalid> wrote in message
news:dto300$or8$1@lust.ihug.co.nz...
> Caught wrote:
>> Help please .....
>>
>> I have been given the task of keeping the accounts [on excel] in
>> order. The business has the sales client name and figures listed per
>> month on different worksheets.
>>
>> When a payment comes in for a specific amount, it would be very
>> useful to go to a function that could search every worksheet. [All I
>> seem to be able to do is the methodically do a 'find' on every
>> worksheet]
>> Is there any way in which the FIND can be used to search every
>> worksheet?

>
> Which version of Excel are you using? In Excel 2003 if you press Ctrl+F
> for find then click on options there is a field called "Within" and u can
> set that to "Workbook" instead of "Sheet".


Pretty ancient ...

Version 8 - Created 21.11.96 ..... it came with Office 97

Sadly it does not have that feature :-(



Crains 02-24-2006 11:54 PM

Re: Excel FIND option request
 
Caught wrote:
> "Crains" <none@none.invalid> wrote in message
> news:dto300$or8$1@lust.ihug.co.nz...
>> Caught wrote:
>>> Help please .....
>>>
>>> I have been given the task of keeping the accounts [on excel] in
>>> order. The business has the sales client name and figures listed per
>>> month on different worksheets.
>>>
>>> When a payment comes in for a specific amount, it would be very
>>> useful to go to a function that could search every worksheet. [All I
>>> seem to be able to do is the methodically do a 'find' on every
>>> worksheet]
>>> Is there any way in which the FIND can be used to search every
>>> worksheet?

>>
>> Which version of Excel are you using? In Excel 2003 if you press
>> Ctrl+F for find then click on options there is a field called
>> "Within" and u can set that to "Workbook" instead of "Sheet".

>
> Pretty ancient ...
>
> Version 8 - Created 21.11.96 ..... it came with Office 97
>
> Sadly it does not have that feature :-(


ouch.. oh well I won't be much help then. Haven't used Office 97 in ages



Caught 02-25-2006 12:01 AM

Re: Excel FIND option request
 

"Crains" <none@none.invalid> wrote in message
news:dto6br$ul8$1@lust.ihug.co.nz...
> Caught wrote:
>> "Crains" <none@none.invalid> wrote in message
>> news:dto300$or8$1@lust.ihug.co.nz...
>>> Caught wrote:
>>>> Help please .....
>>>>
>>>> I have been given the task of keeping the accounts [on excel] in
>>>> order. The business has the sales client name and figures listed per
>>>> month on different worksheets.
>>>>
>>>> When a payment comes in for a specific amount, it would be very
>>>> useful to go to a function that could search every worksheet. [All I
>>>> seem to be able to do is the methodically do a 'find' on every
>>>> worksheet]
>>>> Is there any way in which the FIND can be used to search every
>>>> worksheet?
>>>
>>> Which version of Excel are you using? In Excel 2003 if you press
>>> Ctrl+F for find then click on options there is a field called
>>> "Within" and u can set that to "Workbook" instead of "Sheet".

>>
>> Pretty ancient ...
>>
>> Version 8 - Created 21.11.96 ..... it came with Office 97
>>
>> Sadly it does not have that feature :-(

>
> ouch.. oh well I won't be much help then. Haven't used Office 97 in ages

Thanks all the same



Dave Doe 02-25-2006 03:14 AM

Re: Excel FIND option request
 
In article <dto2ps$o8r$1@lust.ihug.co.nz>, court@ihug.co.nz says...
> Help please .....
>
> I have been given the task of keeping the accounts [on excel] in order. The
> business has the sales client name and figures listed per month on different
> worksheets.
>
> When a payment comes in for a specific amount, it would be very useful to go
> to a function that could search every worksheet. [All I seem to be able to
> do is the methodically do a 'find' on every worksheet]
>
> Is there any way in which the FIND can be used to search every worksheet?
>
>
>
>

some eg's

' cut down the range you Find in...
Range("A3:G3").Select

'select a sheet
Sheets("Payments").Select

'find "test" text
Cells.Find(What:="test", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

Make a new 'find' worksheet, use a cell as your search parameter input
field, put a button beside it, call your function, altered to accept the
search paramet, do something with the result(s).

Some ideas anyway - ps: the above samples all quickly made by recording
a macro.

--
Duncan

Rob J 02-25-2006 04:43 AM

Re: Excel FIND option request
 
In article <dto2ps$o8r$1@lust.ihug.co.nz>, court@ihug.co.nz says...
> Help please .....
>
> I have been given the task of keeping the accounts [on excel] in order. The
> business has the sales client name and figures listed per month on different
> worksheets.
>
> When a payment comes in for a specific amount, it would be very useful to go
> to a function that could search every worksheet. [All I seem to be able to
> do is the methodically do a 'find' on every worksheet]
>
> Is there any way in which the FIND can be used to search every worksheet?


If it is a real business they should use a proper accounting package.

Excel is great for some things but not for organising large amounts of
data, a database is needed for that and most accounting packages use one
because there are so many advantages.

Kong King 02-26-2006 10:47 PM

Re: Excel FIND option request
 

"Caught" <court@ihug.co.nz> wrote in message
news:dto2ps$o8r$1@lust.ihug.co.nz...
> Help please .....
>
> I have been given the task of keeping the accounts [on excel] in order.
> The
> business has the sales client name and figures listed per month on
> different
> worksheets.
>
> When a payment comes in for a specific amount, it would be very useful to
> go
> to a function that could search every worksheet. [All I seem to be able to
> do is the methodically do a 'find' on every worksheet]
>
> Is there any way in which the FIND can be used to search every worksheet?
>
>
>


I think you'd have to write a macro to search through specific files.

You are really doing database work and should store all data in one large
table.
The users think because they have to do reports each month then they keep
the data separated into months.
This is fine on a small scale, or for the first few months, and then you
realise the problems involved.
If you keep each month in a separate file then you are not going to be able
to do any annual reports etc
If you need to do timeseries over several years then you need to extend the
data even further ...

Better to add a month column (or date) and continue the data each month on
the same worksheet.
Then you use data filters or maybe pivot tables to do the reports each
month.
Depends a bit on how many data lines you have each month, but Excel 97 will
take 65,500 lines.
Might also depend on your RAM as to how much you can handle in one file.
MS Access is another option, and you can still do the donkey work in Excel
....

HTH












Alan 02-27-2006 10:12 PM

Re: Excel FIND option request
 

"Caught" <court@ihug.co.nz> wrote in message
news:dto2ps$o8r$1@lust.ihug.co.nz...
> Help please .....
>
> I have been given the task of keeping the accounts [on excel] in
> order. The
> business has the sales client name and figures listed per month on
> different
> worksheets.
>
> When a payment comes in for a specific amount, it would be very
> useful to go
> to a function that could search every worksheet. [All I seem to be
> able to
> do is the methodically do a 'find' on every worksheet]
>
> Is there any way in which the FIND can be used to search every
> worksheet?
>
>
>


Select all the sheets (or the sheets you want to search), then do
Edit - Find.

Remember to deselect all those sheets before making any changes, else
you will be changing all of them!

Backup your file before doing anything, and regularly thereafter.

HTH,

Alan.


--

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

6f7chu602@sneakemail.com

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb





Mercury 03-01-2006 10:24 PM

Re: Excel FIND option request
 
Rob,

I agree. This is how companies get into deep expensive shyte. The
spreadsheet system grows and grows then one day reality strikes "we should
have used a more appropriate product".

I had a company with 30,000 SS's as the result of a "mistake" of this
nature. I turned the work down as there are better things in life to do.



"Rob J" <rob.j@steve.is.strange.nospam.nospam> wrote in message
news:MPG.1e6a9f43dc206608989cd7@news.chc.ihug.co.n z...
> In article <dto2ps$o8r$1@lust.ihug.co.nz>, court@ihug.co.nz says...
>> Help please .....
>>
>> I have been given the task of keeping the accounts [on excel] in order.
>> The
>> business has the sales client name and figures listed per month on
>> different
>> worksheets.
>>
>> When a payment comes in for a specific amount, it would be very useful to
>> go
>> to a function that could search every worksheet. [All I seem to be able
>> to
>> do is the methodically do a 'find' on every worksheet]
>>
>> Is there any way in which the FIND can be used to search every worksheet?

>
> If it is a real business they should use a proper accounting package.
>
> Excel is great for some things but not for organising large amounts of
> data, a database is needed for that and most accounting packages use one
> because there are so many advantages.





All times are GMT. The time now is 03:27 PM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.