# lock to recognise table total

#### collinsc

##### Board Regular
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
Maybe you could use:

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

#### collinsc

##### Board Regular
Thanks Andrew,

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

#### collinsc

##### Board Regular
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

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

#### collinsc

##### Board Regular
ok, thanks very much

Replies
4
Views
147
Replies
8
Views
387
Replies
4
Views
144
Replies
17
Views
471
Replies
6
Views
330

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.

### Which adblocker are you using?

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

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