Finding a value without a specific cell location

Hey all,

I've attached the workbook and hopefully you guys can help me out.
In Sheet 2 i have bunch of data and at the buttom of the sheet, is my summary area.
This summary area contains 7 rows with 2 columns each that gives me a percentage breakdown.
it looks like this.

1 day 2 days 3 days 4 days 5 days 6 days >6 days
68.09% 29.79% 2.13% 0.00% 0.00% 0.00% 0.00%


Basically i'm stuck because in Sheet 1, i can't use HLOOKUP.
The reason i can't use this function is that the summary area's cell locations changes everyday.
So basically in Sheet 2 today they are in rows 53 and 54. but tomorrow it could be rows 70 and 71. I don't want to keep updating the formula everyday.

So ideally, Sheet 1 B6 should have a formula that will return me B54+C54 as a percentage and
Sheet 1 C6 should have a formula that will return me B54+C54+D54+E54 as a percentage.

Then tommorow as the values in Sheet 2 changes, Sheet 1 cells B6 and C6 should be able to adjust so i still get the correct percentages from the Summary area in Sheet 2.

Sorry if this has confused anyone.

Cheers

OK a few points...

First change the formulae you're using in your Summary section - as is you're storing numbers as text which is only going to cause you problems:

Please Login or Register  to view this content.

Then to keep things simple... assuming "1 day" etc always appears in Column B on Sheet 2 and values are always in row below then use this logic to determine your remaining calcs - store this value in any given cell (to avoid repetitive calcs), eg:

Please Login or Register  to view this content.

Your remaining calcs are then pretty straightforward I think...

Please Login or Register  to view this content.