Excel FIND option request

Discussion in 'NZ Computing' started by Caught, Feb 24, 2006.

  1. Caught

    Caught Guest

    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?
    Caught, Feb 24, 2006
    #1
    1. Advertising

  2. Caught

    Crains Guest

    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".
    Crains, Feb 24, 2006
    #2
    1. Advertising

  3. Caught

    Caught Guest

    "Crains" <> wrote in message
    news:dto300$or8$...
    > 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 :-(
    Caught, Feb 24, 2006
    #3
  4. Caught

    Crains Guest

    Caught wrote:
    > "Crains" <> wrote in message
    > news:dto300$or8$...
    >> 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
    Crains, Feb 24, 2006
    #4
  5. Caught

    Caught Guest

    "Crains" <> wrote in message
    news:dto6br$ul8$...
    > Caught wrote:
    >> "Crains" <> wrote in message
    >> news:dto300$or8$...
    >>> 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
    Caught, Feb 25, 2006
    #5
  6. Caught

    Dave Doe Guest

    In article <dto2ps$o8r$>, 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
    Dave Doe, Feb 25, 2006
    #6
  7. Caught

    Rob J Guest

    In article <dto2ps$o8r$>, 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.
    Rob J, Feb 25, 2006
    #7
  8. Caught

    Kong King Guest

    "Caught" <> wrote in message
    news:dto2ps$o8r$...
    > 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
    Kong King, Feb 26, 2006
    #8
  9. Caught

    Alan Guest

    "Caught" <> wrote in message
    news:dto2ps$o8r$...
    > 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:



    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
    Alan, Feb 27, 2006
    #9
  10. Caught

    Mercury Guest

    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" <> wrote in message
    news:...
    > In article <dto2ps$o8r$>, 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.
    Mercury, Mar 1, 2006
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. belfast-biker
    Replies:
    0
    Views:
    1,017
    belfast-biker
    Jan 14, 2006
  2. Carol2

    Request Info AdAware Find

    Carol2, Jan 14, 2005, in forum: Computer Support
    Replies:
    2
    Views:
    380
    Carol2
    Jan 14, 2005
  3. lcorrigan
    Replies:
    2
    Views:
    1,239
    lcorrigan
    Sep 27, 2006
  4. News Reader
    Replies:
    3
    Views:
    519
    Graham
    Oct 26, 2006
  5. ArtL7
    Replies:
    1
    Views:
    1,017
    thanatoid
    Jun 4, 2009
Loading...

Share This Page