Count non-blank cells and put info in a specific cell/sheet

I need to count non-blank cells in a series of sheets and put that info on a summary page. The problem I'm having was trying to use counta - it works fine when the sheets exist but I'm generating the sheets dynamically and I get a #ref! when I go back to the summary sheet after the sheets have been created. It's 1:05AM and I'm not sure if that makes sense so I'll give you the particulars of the workbook.

Sheets that exist in the "template" that pertain to this question:
Summary
GL-045-BW (B6)
GL-040-BW (B5)
GL-SLD-BW (B12)
SP-X40-BW (B7)
SP-X45-BW (B8)
XD-SLD-BW (B9)
SW-M40-BW (B10)
SW-M45-BW (B11)
AR15RC-BW (B33)
ARHNDL-BW (B32)

I need to count the non-blank cells in all of the *-BW sheets and put the values in the Summary sheet in specific cells as noted after the sheet name. I have looked through everything I could find but I'm not seeing anything that makes sense to me...

Any help would be appreciated!

Gary

You might be able to get around the #ref error by using an indirect "hard-coded" reference.
For example, in B6, put something like:
=COUNTA(INDIRECT("GL-045-BW!A1:Z100"))

Thanks that worked with one slight modification =COUNTA(INDIRECT("'GL-045-BW'!A1:Z100")) - needed the single quotes around the sheet name. Now I need to have it conditionally evaluated if the sheet actually exists and has data in cells, it returns 1 even if the sheet doesn't exist. I got around an empty sheet returning 1 with this =IF(INDIRECT("'GL-045-BW'!2:20")>"",1,0), but it returns a #REF! error if the sheet doesn't exist yet.

You probably need to use an error check along the lines of :
=IF(ISERROR(INDIRECT("'GL-045-BW'!A1:Z100")),0,COUNTA(INDIRECT("'GL-045-BW'!A1:Z100")))

That was EXACTLY what I needed, thanks!

Another option (I think).

Please Login or Register  to view this content.

John,
That works really well but I'm trying to figure out how to change a few things - First, I realized that one of the parts doesn't have "BW" in the name, it could end up to be more than just that one so I thought I'd try using the sheet index number instead of the name. Everything past sheet number 9 should be evaluated and I'll always add parts after sheet 9 so that's pretty safe. Second, the first row should be ignored as it contains field info for merging into a Corel file - I would just subtract the number of fields in that first row from "x" but that varies per part. Last, I would prefer to have the page name and quantity in separate cells - it would be ideal to specify where to place each individual page name/value based on the existing spreadsheet, but I'll modify the spreadsheet to accommodate the results of this code instead.

So, to that end I have tried the following but keep getting errors and I don't know how to correct it... The error is "Object Variable or with block variable not set". Obviously I know just enough about VB to get myself into trouble, then I need to find an expert to get me out of it.
here is the code I changed to try and get the counts from sheets with an index of 9 or higher. It does go to sheet 9 and then gives the error - I'm close!

Please Login or Register  to view this content.

Can you attach a sample worksheet with some data to test on? Or try this and see if it does what you want.

Please Login or Register  to view this content.

That fixed it John, thanks! Any ideas on the other two questions? I would post the worksheet but it has some proprietary info in it and it will take a while to clean it out enough to post - I'll do it but it will be a little while before I can get it done.

Maybe:

Please Login or Register  to view this content.

This puts the quantity in Column J

Perfect, thanks!

You're welcome. Glad to help out and thanks for the feedback. Please comply with Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

Hello, sapell. And Welcome to the Forum. It's not good practice to post to a thread that's already marked solved. I'd recommend creating a new thread. That way your questions will not go unnoticed and generate more responses.