Add column which refers to a worksheet

[A[L]C]

Hi all,

I wonder if you can assist me please? I am using excel 2007.

I have set up a workbook which executes a stored procedure on a sql database, which returns the results to a table.

I have a control sheet which allows me to input dates, which pass through to the stored proc. From these dates, it works out the networkdays, and multiplies it by 7.5 to give me the standard working hours. It looks like this:

hoji.png

I then have a pivot table on a different sheet which looks at my data table to give me the total number of logged hours. I need to compare the logged hours to the working hours to give me a percentage. Like this - but inside the actual pivot table so it is dynamic.

sxes.png

Is this possible?

Thanks
Paul

[A[L]C]

I've sort of got round it by using the formula

=IF(A2="Grand Total","Ermmm",IF(A2="","",'Control sheet'!$G$3))

but I'm not sure how I could calculate the Grand Total formula, as this needs to sum everything above its current position (not including the header row).

[Pete_UK]

Try this:

=IF(A2="Grand Total",B2/C2,IF(A2="","",'Control sheet'!$G$3))

though I'm not sure where the formula is on your pictures.

Hope this helps.

Pete

[Pete_UK]

If the formula is in C2, then you can use this:

=IF(A2="Grand Total",SUM(B$1:B1),IF(A2="","",'Control sheet'!$G$3))

I think that's what you need, then you would have a percentage formula in column D.

Hope this helps.

Pete

[A[L]C]

Hi Pete,

Thanks, tried your second (C2) formula, but it's not quite what I was after, as this will grand total column B rather than everything in column C above the position of the current cell.

So with your formula, it shows the grand total in column c as 710.77, but it should show 780 as in my first picture.

[Norie]

Paul

Can you upload an example workbook?

[Pete_UK]

... it's not quite what I was after, as this will grand total column B rather than everything in column C above the position of the current cell...

Sorry, it should have been this in C2:

=IF(A2="Grand Total",SUM(C$1:C1),IF(A2="","",'Control sheet'!$G$3))

Note the changes in the SUM function.

Hope this helps.

Pete

[A[L]C]

perfect, thank you pete

[A[L]C]

Just a shame I can't get it to change the cell format of the bottom row to match the Grand Total of the pivot table

[Pete_UK]

You can use conditional formatting to do that.

Pete