lock to recognise table total

collinsc

Board Regular
Joined
Sep 13, 2006
Messages
230
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!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Maybe you could use:

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

collinsc

Board Regular
Joined
Sep 13, 2006
Messages
230
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
 

Forum statistics

Threads
1,136,654
Messages
5,677,012
Members
419,668
Latest member
DharmaK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top