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 Thanks for your kind help. I didn't try your solution as I found it a bit tricky to understand. But, yes your other suggestions were quite useful. I'll take note of that & implement in my sheet.
I've another issue related to the same project. Now, the sheet I shared was for one month. I'll have twelve such sheets. I'm attaching the sheet. How can I easily get data of 12 months in this single sheet?