lock to recognise table total

[collinsc]

Hi all

I am calculating % of time allocated to projects. I am getting the data from another worksheet.

This is the formula: =’prj hours’!$I$7/’prj hours’!$I$23


The one issue is the last part of the formula “$I$23”
The total wont always be in cell “I 23”.. it might be higher or lower than "I", depending on how many projects there are.

My question is… can I somehow lock the cells so that it recognises where the GRAND TOTAL is on the ‘prj hours’ worksheet?

An example of the sheet with the data:

Prj A 324
Prj B 024
Prj C 254
Prj D 054
Prj E 124

(324, 024, 254 etc... are hours)

Thanks!

 

[Andrew Poulsom]

Maybe you could use:

INDEX(’prj hours’!$I$I,MATCH("GRAND TOTAL",’prj hours’!$A$A,FALSE),1)

 

[collinsc]

Thanks Andrew,

i will put that in and see what it does.!

 

[collinsc]

Andrew
it doesnt like that... i assume i put = before it..?

Should i be placing this formula in a particular row or column?
i.e. in the % column, or in the Grand Total row?

thanks

 

[Andrew Poulsom]

It goes in your original formula in place of ’prj hours’!$I$23.

 

[collinsc]

ok, thanks very much