Problem w/ cell reference
 

I have 12 budgets (vertically) within a sheet. Each budget has two
commandbuttons that sort two different ways. One sort is by date due, the
other separates income and payments, putting income first.

GIVEN: Total rows = 50 per budget. The 51st row shows totals.
I have three columns that show % of whatever

Even though all 12 budgets are created at the first of the year, they are
often updated in the event a new bill is created, or finished paying one off.
This is where I use the separation of income and payments, so I can just
copy the payment portion to the next month.

For January, I set up the % formulas. Example of a formula is
=IF(OR($H7="",$H7=0),"",$H7/$H57), where H:H is the payment amount and H57
is the TOTAL PAYMENTS. Note the lack of the $ sign before the 57. This is
done so that if I copy the payments to the next month, the row would change.

NOW THE PROBLEM: If that month is sorted one way, the cell reference
changes when I click the other command button, so $H57 may change to $H63,
when it needs to stay $H57. Again, I don't want to use the $ in front of the
57.

Is there another way to refernce $H57 with using the $ sign and without
having it change regardless of the sort?


Thanks,
Les

Problem w/ cell reference
 

Assuming you don't want to do this for ALL, name it.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"WLMPilot" wrote in message
...
I have 12 budgets (vertically) within a sheet. Each budget has two
commandbuttons that sort two different ways. One sort is by date due, the
other separates income and payments, putting income first.

GIVEN: Total rows = 50 per budget. The 51st row shows totals.
I have three columns that show % of whatever

Even though all 12 budgets are created at the first of the year, they are
often updated in the event a new bill is created, or finished paying one
off.
This is where I use the separation of income and payments, so I can just
copy the payment portion to the next month.

For January, I set up the % formulas. Example of a formula is
=IF(OR($H7="",$H7=0),"",$H7/$H57), where H:H is the payment amount and H57
is the TOTAL PAYMENTS. Note the lack of the $ sign before the 57. This
is
done so that if I copy the payments to the next month, the row would
change.

NOW THE PROBLEM: If that month is sorted one way, the cell reference
changes when I click the other command button, so $H57 may change to $H63,
when it needs to stay $H57. Again, I don't want to use the $ in front of
the
57.

Is there another way to refernce $H57 with using the $ sign and without
having it change regardless of the sort?


Thanks,
Les

Problem w/ cell reference
 

Thanks! Figured it was something simple.

Les

"Don Guillett" wrote:

Assuming you don't want to do this for ALL, name it.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"WLMPilot" wrote in message
...
I have 12 budgets (vertically) within a sheet. Each budget has two
commandbuttons that sort two different ways. One sort is by date due, the
other separates income and payments, putting income first.

GIVEN: Total rows = 50 per budget. The 51st row shows totals.
I have three columns that show % of whatever

Even though all 12 budgets are created at the first of the year, they are
often updated in the event a new bill is created, or finished paying one
off.
This is where I use the separation of income and payments, so I can just
copy the payment portion to the next month.

For January, I set up the % formulas. Example of a formula is
=IF(OR($H7="",$H7=0),"",$H7/$H57), where H:H is the payment amount and H57
is the TOTAL PAYMENTS. Note the lack of the $ sign before the 57. This
is
done so that if I copy the payments to the next month, the row would
change.

NOW THE PROBLEM: If that month is sorted one way, the cell reference
changes when I click the other command button, so $H57 may change to $H63,
when it needs to stay $H57. Again, I don't want to use the $ in front of
the
57.

Is there another way to refernce $H57 with using the $ sign and without
having it change regardless of the sort?


Thanks,
Les