Hi, I have multiple worksheets each containing a financial statement like the above. Each worksheet has a different company name. Each worksheet's type of revenue and expenses could be different.
I need to combine all of them into a worksheet of predetermined format, thus pivot table is not a consideration. All the worksheets and the summary worksheet is in the same workbook.
In the summary worksheet, I have a check box to select which company I want to be included in the summary. So everytime I check a company, the name would appear in A1:A10. The way the formula is setup now is a multiple IF statements hard coded in each cell. ie. IF A1=HAPPY,HAPPY!D14,0)+IF(A2=SAD,SAD!D14,0+IF(A3=MAD,MAD!D14,0)+IF(A4=JOY,JOY!D14,0)
So the more companies I have, the more IF I have to add-on. Now, I can use the INDEX(MATCH()) to grab the info I need from each company to the summary worksheet.
The problem is how to sum each number according to which companies are selected.
I hope I'm explaining it clearly. Is there a formula or VBA procedure to look at a range of worksheet names and sum a particular cells from the different worksheets?