I have a number of similar Calc spreadsheets, each covering a calendar year and containing a separate sheet for each month. I am looking for the entry relating to a single (unusual) keyword, but don’t even know the relevant year. I need to search each spreadsheet in turn until I find it. Can I search a whole spreadsheet at once, or must I search each component sheet (which will take forever)?
Concerning the recent answers:
As I understand the OriginalQuestioner, he (mis)uses the term “Calc spreadsheets” for a set of spreadsheet documents, one per year. On his search for a specific keyword, so he (f/m) emphasizes, he also has the problem sometimes, not even to know the year.
Addressing this I would like to add:
An automated search accross (e.g.) all the spreadsheet documents saved to a specific folder (and having a specific filename pattern hopefully) is only feasible resortinhg to user code.
Generally: The one-per year, the one-per-month, and similar designs get reinvented again and again. The obvious problems related to the idea are exemplified by this question again. Therefore:
Managing data with spreadsheets is doubtable anyway, but possible to some degree. As soon as the data can no langer be kept in a single sheet or even not in a single file, the pool (data, reports, and everything) must be moved to an actual database.
@Lupp - I understood the “misuse” in using the term spreadsheet as a synonym for a single table within a spreadsheet (calc) document and a complete (but single) spreadsheet (i.e. calc) document simultaneously. May be I’m wrong and then, of course, my answer wouldn’t make much sense.
The shorthand question is misleading anyway. How to search a single sheet every user already having produced files over years surely must know. He may have missed,however, the option All sheets.
Quoting from the question: “I have a number of similar Calc spreadsheets, each covering a calendar year and containing a separate sheet for each month.”
I cannot interpret this in a different way than I already did.
The attitude of users to be indifferent regarding the clarity and precision of their own wording, but to expect a tutorial (kind of) in place of an answer seems still to increase - if possible at all.
Anyway the questioner meanwhile told you he was satisfied with your answer. The problem with unknown years seems to have vanished.
To clear up what I did, calling each collection of sheets a “file” - I searched each year’s file in turn, and hit the jackpot on the second I examined.
However, I still don’t know how I should refer to these collections of pages, if not as “spreadsheets”. The title of each is “Expenses 2020”, “Expenses 2019,” “Expenses 2018”, and so on.
The terms are:
Spreadsheet for a single sheet (containing cells strucured by columns and rows). Every sheet has a name (unique, shown on the tab of the sheet) and an index (zero-based, managed automatically) inside the Spreadsheet document it belongs to.
The spreadsheet document (as any kind of document LibreOffice works with) can be saved to a file. The default (and strongly recommended) extension to the filename is .ods in the given case.
Pages aren’t content objects of documents, but just subdivisions created “on the fly” for puposes of the view or the printing. Even if you insert a manual page break somewhere, it is only recognized as an additional attribute of something else (a paragraph in case of Writer, a row or a column in case of Calc).
All this is just an “extra” or “spoken aside”.concerning your question.
My relevant comment was (using whatever terms) the first one concerning the one-per-year documents and the one-per-month sheets.
You cannot effiently search across files. (“Not efficiently” meant as “next to not at all”.)
You cannot search in a handy way accross sheets.
You cannot efficiently evaluate contents by formulas and/or standard tools of spreadsheet software (PivotTable, Sort, Filter, …) if the data are scattered over many sheets or even files.
What looks like a structuring aid to the human eye if a one-sheet-per-month design is chosen, is a very serious aggravation concerning the purposes the software is made for.
Hello,
use find from Edit -> Find & Replace (CTRL+H) and select options [x] All sheets
Hope that helps.
Thanks very much. Found it within a couple of minutes.
Great!
Use Edit | Find and Replace and check the box All sheets. The screenshot shows the result of a search in a spreadsheet where sheets 1, 2 and 3 all contain the same data in A.
If this answer helped you, please accept it by clicking the check mark ? to the left and, karma permitting, upvote it. That will help other people with the same question.
In case you need clarification, edit your question (don’t use an answer) or comment the relevant answer.