Entering text value representing a sum in Microsoft Excel

Under "Monday" where I have typed rent, I would like that cell to display the text "Rent" still, but to represent the value of rent on row 4. For the formula in "balance" to work I know it needs to be a sum, so I'd like it to be recognised as a sum.

I only want to be able to type in rent and have it still display as rent because it's easier for me to look back at it rather than numbers.

Thanks

-

migrated from stackoverflow.comDec 18 '12 at 18:33

This question came from our site for professional and enthusiast programmers.

An alternative (also ugly) is to adjust the balance formulae with something like -COUNTIF({range},"Rent")*320, where the '320' could be replaced by the reference to the cell (in Row4). This does not cope with a rental payment as well as some other payment on the same day, but you don't mention how you would propose to display that.

By value alone would seem to stand out clearly enough and otherwise inserting a comment (as @Daniel Cook) seems much more sensible to me!

-

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:

1. Assign a Name to the cell containing the Rent value. (Easiest way is via Name box left of Formula bar)
2. Indicate Rent in the cell where you want it's value applied. (Just as you did)
3. Anywhere that actually needs that value get the value by using Indirect. Like so: Indirect(C9).

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:

=SUM(IF(ISERROR(INDIRECT(C16)),C16,INDIRECT(C16)),IF(ISERROR(INDIRECT(C17)),C17,INDIRECT(C17)),IF(ISERROR(INDIRECT(C18)),C18,INDIRECT(C18)),IF(ISERROR(INDIRECT(C19)),C19,INDIRECT(C19)))

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.

-