Referencing Cells Based on a Calculated Value for the Cell-ID
 

I have a simple worksheet that tracks my mortgage payments. At the top of
the sheet I've created a summary section that lets me see the impact of
changes (e.g., amortization period, interest, lump sum payments, etc) made
to my mortgage. I've also created a cell to report my current balance
(based on today's date).

Normally, to populate one cell (say cell E25) with the contents of another
(say cell H57) you would simply insert =H57 in cell E25. No problem.

BUT, if the cell address varies according to today's date, how do I insert
the result of a calculation to point to the referenced cell?

The calculation I use to determine the desired cell identifier for the
current balance is:

=CONCATENATE("column_identifier",(INT((TODAY()-C2)/14)+5))

WHE
column_identifier is the column letter for the balance entries
following bi-weekly payments
C2 is the cell containing the start date for the mortgage
INT returns an integer result for: {[(today's date) - (start
date)]/14} . . . the division by 14 relates to bi-weekly entries
+5 represents the necessary offset in row numbers to identify the
correct "current" entry

The result of this calculation gives me the correct cell reference for the
current balance information BUT I do not know how to use it to retrieve the
related contents in the Summary cell.

I've tried stacking the CELL(["contents"],[reference]) function ----
CELL("contents",CELL("contents",[cell reference for the concatenation
calculation])) --- but that syntax is invalid.

Any suggestions ????

Dave

Referencing Cells Based on a Calculated Value for the Cell-ID
 

Hi Dave,

Wrap your formula in the INDIRECT function:

=INDIRECT(CONCATENATE("column_identifier",(INT((TO DAY()-C2)/14)+5)))

This will dereference the address returned by your formula and give you the
value in that cell.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"David Graves" wrote in message
...
I have a simple worksheet that tracks my mortgage payments. At the top of
the sheet I've created a summary section that lets me see the impact of
changes (e.g., amortization period, interest, lump sum payments, etc) made
to my mortgage. I've also created a cell to report my current balance
(based on today's date).

Normally, to populate one cell (say cell E25) with the contents of another
(say cell H57) you would simply insert =H57 in cell E25. No problem.

BUT, if the cell address varies according to today's date, how do I insert
the result of a calculation to point to the referenced cell?

The calculation I use to determine the desired cell identifier for the
current balance is:

=CONCATENATE("column_identifier",(INT((TODAY()-C2)/14)+5))

WHE
column_identifier is the column letter for the balance entries
following bi-weekly payments
C2 is the cell containing the start date for the mortgage
INT returns an integer result for: {[(today's date) - (start
date)]/14} . . . the division by 14 relates to bi-weekly entries
+5 represents the necessary offset in row numbers to identify the
correct "current" entry

The result of this calculation gives me the correct cell reference for the
current balance information BUT I do not know how to use it to retrieve
the
related contents in the Summary cell.

I've tried stacking the CELL(["contents"],[reference]) function ----
CELL("contents",CELL("contents",[cell reference for the concatenation
calculation])) --- but that syntax is invalid.

Any suggestions ????

Dave