Pivot Charts from Sharepoint using filtering and Offset formula

[rupeshm]

Hi,


I need to create weekly chart report from a sharepoint site.

This is what i do to extract the data.

1. Pull data from sharepoint (Using export as spreadsheet option)

2. Create Pivot Table from the List of data. (Time as Row labels and count of work status as column label and values)

3. Filter the Pivot table (row label) for last week data.


Finally i see the data as required. Now i want to generate the report from the grand total for the column labels.

Time , Work A, Work B, Work C, Grand Total

Day 1, 0, 1, 1, 2

Day 2, 0, 0, 1, 1

Day 3, 1, 1, 0, 2

Day 4, 1, 0, 1, 2

------------------------------------ (only for separation)

Total 2, 2, 3, 7


In the chart for above table i need Work A, B, C on X-axis and the total corresponding values on Y-axis.

The Row and Column values are dynamic as the data is coming from sharepoint.


CountA formula provides me the dynamic data range. whereas to generate chart i am using offset formula to select specific range. Now weekly as data will change the row and column also changes so the offset formula needs to take care of it.


To Get the Values Work A, B,C i am using the formula

=OFFSET('Last Week'!$B$1,0,0,1,COUNTA('Last Week'!$B$1:$Y$1))


To Get the Total Values for Work A,B, C

=OFFSET('Last Week'!$B$6,0,0,1,COUNTA('Last Week'!$B$6:$Y$6))

Now as the data changes weekly the reference cell value will change. $B$6 changes with values. i can get the value 6 (for $6) from =COUNTA('Last Week'!$A:$A), but somehow cannot add to offset formula.

Example

=OFFSET('Last Week'!$B$(COUNTA('Last Week'!$A:$A)),0,0,1,COUNTA('Last Week'!$B$(COUNTA('Last Week'!$A:$A)):$Y$(COUNTA('Last Week'!$A:$A))))


Maybe i am missing something basics here but any help would be highly appreciated.

Any ideas how to achieve this?


Thanks and Cheers!

 

[Hui]

Rupeshm


Firstly welcome to the Chandoo.org Forums


I think you might need something like:

=OFFSET('Last week'!$B$1,COUNTA('Last week'!$A:$A),0,1,COUNTA('Last week'!$A:$A))


You don't need to work out the cell references as the Offset can do that

So set an anchor point

Work out the Offsets to the Top left of your data area Y_Offset, X_Offset

Work out the size of your data area, Y_Size, X_Size

=Offset(Anchor, Y_Offset, X_Offset ,Y_Size, X_Size)

 

[rupeshm]

Hui

Thanks for the welcome message.


Really appreciate your prompt response. Your recommendation worked like a charm.


Thanks a lot for your valuable time and effort.


Cheers.

- A Happy Chandoo.org Member