FORMULA FOR REPORT IN EXCEL

[Sathisc]

Hi,

I am currently working in a shared excel, I have a raw data in Sheet 1 (Data) and i need to create report in a format which is in Sheet 2(report).
Help is appreciated

Cheers,
Sathish

[Bob Phillips]

Just pivot the data, it will do exactly what you want.

[Sathisc]

Thanks for the reply but the sheet is shared i am not able to refresh the table.

[Bob Phillips]

Ah, the perils of shared workbooks.

[shrivallabha]

There is one trick, I found it out on : Football World Cup 2010 match table. All you need to do is:
1. Insert a clip art on the report page.
2. Create a parallel workbook (non-shared) with the same data and pivot table as XLD has suggested.
3. Now select the clip art and in the formula bar, assign it the reference of Pivot Table.

4. When you get updated data in the shared workbook then paste the same data on the second workbook.
5. Refresh the pivot data on the non-shared workbook.
6. The clip art is updated automatically.

I am attaching the sample.

[aravindhan_3]

Sathish,

Use the sumproduct formula if you like.

Enter this formula in sheet 2 B2
=SUMPRODUCT(--(Data!$B$1:$B$65000=Report!$A2),--(Data!$C$1:$C$65000=Report!B$7))and drag down & right