SOLVED

consolidate data

Copper Contributor

I have a workbook with 31 (they are actually dates of a month) exactly same worksheets. Each sheet has a few cells that contain the total of different columns. Column A of the summary sheet contains all the dates of the month. Now I want to consolidate data of these 31 sheets in a summary sheet. For example, in the summary sheet, cell b4 is to get data of sheet no. 1 cell b206, 

Similarly, In summary sheet, cell b5 is to get data of Sheet no. 2 cell b206. So on & so forth.

 

One way to get it done is by entering cell references from all 31 sheets in all cells of the summary sheet. But that is too much time consuming laborious task. What could be the other solution?

 

I'm attaching the file. Can someone help me to solve this challenge?

 

Warm regards,

 

Rahul Dev

5 Replies
best response confirmed by aredev (Copper Contributor)
Solution

@aredev 

That could be

=IFERROR(INDIRECT("'" & DAY(A4) & "'!B206"),0)

@aredev 

 

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.

@Sergei Baklan Thanks for your kind help. Your solution worked quite well. As it will take at least one month when I have sufficient real-life data to test it. I think it should work. I'll get back to you if I need more help on this issue. 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?

@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?

@JMB17 Greetings Friend! :grinning_face: :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 @Sergei Baklan. 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

 

1 best response

Accepted Solutions
best response confirmed by aredev (Copper Contributor)
Solution

@aredev 

That could be

=IFERROR(INDIRECT("'" & DAY(A4) & "'!B206"),0)

View solution in original post