Hi
I have a number of sheets in an excel workbook with data tables created by a sql query. I would like to get the number of rows with data from each sheet onto a summary sheet. What is the formula for this?
pic1.PNG
For example the attached image has 2 rows of data in one sheet, so I need to refer to the sheet name and the number of rows in a table in a summary sheet as below:
pic2.PNG
Appreciate any help
Regards
This is not an especially hard problem but it's not just a matter of "the formula" and the answer has to be very specific to how your file is organized, and we don't have those details.
From your screenshot it looks like you are using structured tables. The result is a lot easier if you refer to the table name instead of the sheet name that contains it. For example, if your table is called Table1, the count is
Formula:
Please Login or Register to view this content.
Table names are unique so this does not depend on which sheet it is on.
If you need more specific help please attach a sample file so we can see how your sheets are set up. See yellow banner at the top of the page.
Hi Jeff
Thanks. I figured out the formula =ROWS(Table1) after posting this question. However is there a way to get the table name from the sheet number? My summary sheet looks as follows. So is there a way i can write a formula to get the table name from the sheet number and then use it in the ROWS() formula?
Attachment 878339
You can't retrieve a table name or table data based on sheet name without using VBA. Is your workbook structure dynamic, or do you just need a one-time setup?
For a more detailed solution it will help if you attach a sample file.
Here is a sample sheet attached.
If your table position is fixed,you can try this in D2 and copy down.
Please Login or Register to view this content.
Or try this:
=IFERROR(ROWS(TOCOL(INDIRECT("'"&C2&"'!A2:A1000"),1)),"")
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
windknife's answer worked. I am not sure how to mark this as answered.
So does mine, and it's shorter.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.
Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Thanks Ali. Sorry i misunderstood - i thought the first formula is for dynamic tables and yours is for static ones. I have now used yours. Also thank you for the instructions on how to mark this as Solved. Regards
No, they both do the same thing - both need drag copying down.
Thanks for the rep.
Can I extend this question to get the formula to count rows with a certain value in the column? Please see attached sample sheet.
Try this in D2.
Try this in E2.Please Login or Register to view this content.
Please Login or Register to view this content.
Thanks for the suggestion. That worked like magic.
You are welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks