Forum Discussion
Need a count of *every* entry in a workbook
So I'm using Excel to create the framework for a catalog of a glass slide collection. One spreadsheet alone has nearly a hundred tabs, all named uniquely with an abbreviation of the slide name, each tab having from 1 to over 100 entries. Is there any formula (I'm fine with macros!) that could tell me how many entries are in each workbook, excluding the header row on each page? Maybe one that could count the total number of ID field entries? This would be VERY useful, because I stopped counting manually after 12,000 entries. I've included the smallest file below, for testing purposes. Thanks!
If you can live with a quick-and-dirty solution by adding two empty sheets "first" and "last", have a look at the attached file. I entered a few formulae in the "-Legend-" tab.
7 Replies
Ideally, I'd like something that *wouldn't* require naming every single tab in the formula. This is basically so we can get a better idea of just how many individual slides there ARE. Excel's already starting to choke on the largest file, which is uploaded below. (And I'm still adding to ALL 27 files!)
If you can live with a quick-and-dirty solution by adding two empty sheets "first" and "last", have a look at the attached file. I entered a few formulae in the "-Legend-" tab.
Riny_van_Eekelen I don't mind quick and dirty at all! This looks workable, thanks!
Just a question for you: this is obviously a massive database, but in general (and I emphasize that, in general) you'll find we recommend creating a database in Excel as a single table, using a column or two to differentiate between entries. In fact, it looks as if you've already uniquely identified each row, such that even IF you were to put them all into a single sheet, you'd not even need to add a column.
And having all these slides in a single table would enable (I would think) far easier search and locate, easier overall summaries, easier over all database functions in general, than having to first find the unique tab and then search, summarize, whatever.....
So my question, which by now is obvious: What if anything precludes your putting all of these into a single table? There may well be a solid reason; I just (my consultant self) have to ask.
mathetes Because Excel doesn't like over 20000+ tabs in one workbook. And that is NOT an exaggeration. Also, I need to be able to add slides to a tab as I uncover more and more slide boxes. These have only ever been roughly inventoried, NEVER organized beyond cursory attempts. The final database may be in Access, though IT may want it in SQL. This is just the raw data entry step.
