I don't know how to describe it. Can I do this?

[Drywit44]

Row 1 is a series of years, 2020, 2021, etc. Row 2 is projected balances in an investment account. I want to add a hard number to the account balances entered into row three in a given year which I enter in row 4. I want Excel to recalculate the series of balances automatically as I change the year I enter in row 4.



Thanks in advance.

[jeffreybrown]

This sounds possible, but how about attach a sample workbook with a clear explanation of a before and after.

[Drywit44]

Here is a simple worksheet illustrating my question.

[jeffreybrown]

A formula cannot replace what is already in a cell. The only way this can be accomplished is with VBA.

How about using an additional row?

[ArviLaanemets]

Having your workbook designed more database-like will work better. The current design is suspiciously like to any bookkeeping ledger from 1800+.


In attached example, you find 2 different formulas to calculate yearly amounts. The first works only, when year numbers in table don't have any gaps. The second one needs an additional helper column (RowNo, which can be hidden), and it allows gaps in year numbers.