This is not easily achieved, the only method I can think of is a fairly complicated work around which has it's own downsides. Here goes:
- Assign a Name to the cell containing the Rent value. (Easiest way is via Name box left of Formula bar)
- Indicate Rent in the cell where you want it's value applied. (Just as you did)
- Anywhere that actually needs that value get the value by using Indirect. Like so:
However, because sometimes you might want to actually include numbers instead of the name of a named range, you'd have to trap for that.
The updated formula would be something like this:
=IF(ISERROR(INDIRECT(C9)),C9,INDIRECT(C9)) which is pretty ugly, but feasible. Just to show how ugly it would get, here's how you would get the sum of all 4 weeks for a day using your worksheet format:
You may want to look into inserting comments instead. Those will appear upon hovering over the cell. otherwise you can add another column that you use to indicate what type of transaction occurred.