PDA

View Full Version : FORMULA FOR REPORT IN EXCEL



Sathisc
06-22-2010, 03:38 AM
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
06-22-2010, 04:03 AM
Just pivot the data, it will do exactly what you want.

Sathisc
06-22-2010, 04:09 AM
Thanks for the reply but the sheet is shared i am not able to refresh the table.

Bob Phillips
06-22-2010, 04:39 AM
Ah, the perils of shared workbooks.

shrivallabha
06-22-2010, 10:15 PM
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
02-28-2011, 10:26 PM
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