Forum Discussion
Loop Through Worksheets and Counts Populated Rows
I want to create a macro that I can run each week. I have a workbook and on the first worksheet I want to count how many populated rows there are on the other worksheets. So in this example I want a...
Thanks Patrick - can you elaborate please? The simpler the better for me, as I am by no means an excel expert. Thanks in advance.
The formula would look like something like this:
=LET(Stack,VSTACK('A:C'!$A$2:$C$100000),filtered,FILTER(Stack,TAKE(Stack,,1)<>""),ROWS(filtered))In this demo, I have sheets A, B, C. Please see attached workbook.
- So that works for me, but for one sheet only. I had replaced 'A:C' with the name of my first worksheet (Mohan) to the name of my last worksheet (Terry), so it reads =LET(Stack,VSTACK'Mohan:Terry''!$A$2:$C$100000),filtered,FILTER(Stack,TAKE(Stack,,1)<>""),ROWS(filtered)) and I get a #NUM error. If I just use Mohan, then it works but I have to replace the name as I move down each row
Are you able to share an anonymized copy of the workbook?
Edit: here's the formula with your sheet names:
=LET(Stack,VSTACK(Mohan:Terry!$A$2:$C$100000),filtered,FILTER(Stack,TAKE(Stack,,1)<>""),ROWS(filtered))Here's my sheet placement (notice the summary is right of the sheets I'm totaling:
- That's exactly what I changed, but it doesn't seem to work for me. It'll be operator error! 🙂