Excel 2007 : Linking formulas to labels rather than specific cells

I am trying to set up a more permanent set of formulas to check data that changes its position each year.

The easiest way to explain my dilemma is to give an example. In cell A1 there is "Bob's Store" and in cell B1 there is "85", and in cell A2 there is "Jen's Store" and in cell B2 there is "84."

In cell D1 there is "Bob's Store" and in E1 there is "37", and in cell D2 there is "Jen's Store" and E2 contains "34".

There is a formula in cell H1, =B1+E1 and in H2 there is =B2+E2.

However, from year to year, Jen and Bob's stores switch positions. Is there a way to lock the formulas in H1 and H2 so that H1 always adds the totals for Bob's Store and H2 always adds the totals for Jen's store (even if the next year the Jen's figures were in row 1 and the Bob's were in row 2)?

Thanks!

Thanks!

Here is my attempt at an example:

A1 B C D E F G H I J
2 2010
3 Employee Sales Total Employee Commission Total Employee Total
4 Bob 558 Jenn 40 Bob =SUM(D7,G9)
5 Mary 478 Greg 35 Mary =SUM(D8,G12)
6 Jenn 435 Bob 31 Jenn =SUM(D9,G7)
7 Whitman 415 Whitman 28 Whitman =SUM(D10,G10)
8 Greg 375 Wendy 27 Greg =SUM(D11,G8)
9 Wendy 333 Mary 22 Wendy =SUM(D12,G11)

A1 B C D E F G H I J
2 2011
3 Employee Sales Total Employee Commission Total Employee Total
4 Whitman 501 Jenn 37 Bob =SUM(D7,G9)
5 Jenn 488 Whitman 32 Mary =SUM(D8,G12)
6 Greg 454 Greg 31 Jenn =SUM(D9,G7)
7 Mary 435 Bob 29 Whitman =SUM(D10,G10)
8 Wendy 397 Mary 28 Greg =SUM(D11,G8)
9 Bob 381 Wendy 26 Wendy =SUM(D12,G11)


Here is what happens if I just resort when I enter the data for the next year into the same cells. Ideally, the formulas would adjust so that they still show each employees totals.

Thanks!! katie

Your information and the formula is very scattered and divided. Is that the only way for you to capture the information?

Your formulas also shows to headings and empty cells?