Forum Discussion
consolidate data
Another option - I believe you could build the formula for the first row:
Cell B4: ="='"&DAY($A4)&"'!B206"
Cell C4: ="=COUNTIF('"&DAY($A4)&"'!D3:D203,""Y"")"
Cell D4: ="=COUNTIF('"&DAY($A4)&"'!D3:D203,""N"")"
Cell G4: ="=COUNTIF('"&DAY($A4)&"'!F3:F203,""ADR"")"
Cell H4: ="=COUNTIF('"&DAY($A4)&"'!F3:F203,""PC"")"
Cell I4: ="=COUNTIF('"&DAY($A4)&"'!F3:F203,""N"")"
Then, copy the first row down for the rest of your table. Then, copy/paste special value these columns. Then, you have to re-confirm the formula to get excel to recognize it as a formula, which you can do by selecting the column (B4:B34) and then click the data tab, text to columns (don't select any delimiter and just click finish).
I noted there was no sheet 31, so I added it. It appears some of the formulas that compute the percentages had incorrect cell references for the denominator. Also, as a suggestion, I noticed you have IF functions to test for zero and return blank for your calculated fields, which returns errors when you try to compute the percentages (if total Y's were zero then the formula tries to divide "" by a number). I changed the formulas to return zero instead of blank, but changed the cell formatting to hide the zeroes.
See attached.
JMB17 Greetings Friend! 😀 :bouquet: Last night after I wrote the mail to you, today afternoon I thought of trying your solution too. I'm enclosing the workbook with dummy data & its output in the summary sheet. I didn't get the desired result. I'm also enclosing the sheet with the solution given by SergeiBaklan. It gave me the desired result.
Yes, I adopted your suggestion of keeping zero instead of blank though I didn't any error in my formula anywhere. It made more sense to reflect zero instead of blanks.
I would be obliged if you can provide me with a solution for the other workbook. I'm going to have 12 workbooks like the one I uploaded for each month. I want to consolidate the data of all these 12 workbooks (months) in one single workbook.
Warm regards,
Rahul Dev