Office '10

If I wish to display a short notation in a cell that prints in the cell location of a cell that contains a formula I append the following syntax to the resident cell formula: &" *" (where the * is the intended display character).

Is there a way to achieve this same result in a data (non formula) cell?

Btw, Adding a comment is useless since it will not print at the cell location.

Mark

Yeah, but only by turning it into a character string. It isn't hard, though. Say you have the value 5 in a cell; change it to "5 *". I just tried it and the quote marks aren't necessary, but the result is a string, not a numeric any more. If what you had was a string already, then of course that's not a problem.

Your answer presumes I know how to work with strings--unfortunately not. I looked up strings and I've no idea which of the many types you think I could use in conjunction with the 5 * example.


Mark

No, you're quite right. That's what I meant when I said that adding the '*' makes the value a character string rather than a number—it means you can no longer do arithmetic with it. When the contents of a cell are already a character string (as when you add &" *" to the end of a function) then it doesn't matter; but when you want to take the contents of the cell and do further arithmetic with it, you either cannot have the asterisk at the end, or whatever uses that cell has to convert it back to a number before continuing. Usually it's not worth the bother.

Well, let's back up. A numeric value is something you can do arithmetic with, like 5 or 1000 or pi. A character string is non-numeric: 5 + 1000 = 1005, but "Mark" + "Excel" is just nonsense. You can't do arithmetic with characters.

Even "5" + "1000" (note the quote marks) is nonsense; the values 5 and 1000 can be added, but the characters "5" and "1000" cannot. Excel is willing to evaluate ="5"+"1000" and return 1005, but it does it by checking the character strings, noticing that they can be converted meaningfully into numbers and doing so before doing the addition. Try saying ="5"+"1xxx"; when Excel sees the plus sign it'll try to convert both strings into numbers, but when "1xxx" cannot be converted it'll end with the #VALUE! error. That's what I get when I try ="5 *"+1000, too.

Now, I gather you're creating formulae that generate numeric values, and then adding a space-and-asterisk combination to the end. Excel does the same thing there only in the opposite direction; it converts the numeric result to a string, and then concatenates " *" at the end. After that, the contents of that cell are a character string, and you can't add anything to "5 *" any more than you can to "Mark" or "1xxx".

So the short answer is "sure, you can add an asterisk to the end of a non-formula value. But you can't use it for arithmetic afterward".

The longer answer is "...unless you first strip off the asterisk". For instance, if you know that A1 has "1005 *" in it, then in B1 it would be pointless to say =A1*3. But you could take away the asterisk and then multiply:That turns "1005 *" into "1005". It's still a character string, but when Excel sees that you want to multiply it, it'll convert it into a valid number and return 3015.

That works great if you already know there's a space-asterisk at the end of the value. If there might or might not be, it doesn't help (because if there isn't, it'll convert 1005 into 10 before doing the arithmetic). And for all I know you might also have space-asterisk-asterisk out there, too. Even that can be got around, but it gets to be more and more trouble and eventually you will (I predict) decide that it's too much trouble.

But we can still talk, if you want to look into the complications.

>>>>and eventually you will (I predict) decide that it's too much trouble>>>>

Thanks for the careful explanation. Your above quote is my resolution :-)

Mark

Not sure this applies to your particular case, but might help someone who lands here searching:

When I need to annotate/flag a number to be visible in a print version (or just to be more obvous and easier to use than comments, such as using auto-filters), but still need to perform calculations based on the cell's value, I have three different solutions.


First, I use cell color or some other formatting. For example, if I need one or two different flags, I'll set the backgrounds to variations of grey scale (make sure pairs are of easily distinguishable colors when printing to a BW printer). Plus, this option allows for conditional formatting so I don't have to go look for the values myself. Some other formatting options include adding a border to the cell or increasing font weight, changing to italics, or using line-through. Conditional formatting is too complex to explain here, but is certainly worth the time to investigate.


The second option is to add a narrow column just after the column that contains the values I want to annotate and enter the annotationing characters there. Insert an entire column, not just the cells near those that require annotation to avoid messing up any other formulas. This, too, can be a formula that follows rules to annotate based on values to be annotated, the row number of the cells, other cells, named ranges containing formulas, or any combination of these options.


Both options will show up when printed, allow the values to be used calculations without fancy de-texting operations, reduce workload by avoiding modification of individual formulae, reduce the potential for errors if the same sheet is used later (left over annotations), and make you look really SMRT, smart. Oh, and they work well together. You can use line-through on values that should be ignored or skipped and then use a formula to annotate the reasons in the next column.


Finally, if the annotation rules are simple, use numerical formatting. In the format cells dialog box under the number tab, use the custom format category to add characters based on values. How to use this option goes beyond this forum entry but it is worth the time to learn how this works. I once used this to add measurement abbreviations to a set of values, but could still calculate based on the values. Plus, I once used this to assign three different alignment options to one cell (first value aligned left and second value to the right). No matter how much I opened the column, each value aligned to the edges of the cell. Plus, the center had a pipe to separate the values, which was centered.

Back in the bad old days of Excel 2003, using this option combined with conditional formatting was the only way, besides macros, to get more than three conditional formats.


Really brave? Try all three options at once! Or just learn VBA.

Thanks--already tried adding the column previous to the post and that's what will likely be used.

Mark

Mr Geren, I should have thought of suggesting color coding; I use it often enough. But what I never thought of is including the space-asterisk combination in the formatting! That's ingenious, and solves his problem at least to some extent. It may still be a pain maintaining the various markers, but at least it doesn't spoil calculations.

Mark, if you didn't understand that part of David's suggestions, you should ask; it may well be worth your while, if you feel that the extra column is a second-best solution.

Thanks, Bob. A couple of other concepts make that option attractive. Once applied to any cell, that workbook will have the custom format available to use in the custom format menu, even if all the cells using it are cleared of formats. Also, it can be applied in several different time-saving ways.
A macro with a dedicated button and/keystroke to apply it to any selected cells (tho that's not as good an option for beginner's).
Or, use the dialog box to apply to one, then use the format painter to apply to subsequent cells. Or, apply once, then use [ctrl]+[y] to apply it to subsequent cells.
Or, select all the cells that require it with [shift] and/or [ctrl], then apply the format either with a button or through the dialog box.
Or, if the rule for when to annotate is simple, you can build criteria into the custom format and apply to the entire column of values. Set it and forget it method.
Of course writing complex criteria in VBA to apply as needed is an option for some, but not most.

However, the cons for using it is the learning curve. It's not any easy system to figure out how to apply criteria beyond the basic positive, negative, zero, non-number syntax And it can be difficult to troubleshoot, modify, or even understand how it's working if someone else inherits the file. I try to make my files user friendly to the next sucker, I mean user, down the line, but sometimes all you can do is implement the complex solution, then add comments. Better still, use a separate tab to make notes. This sometime saves you the embarrassment later when someone asks how something works and you can't remember. That happens a lot.

But now I'm off topic...

David's conditional formating suggestion would likely work were it not for the difficulty.

After reading up on the technique, it is absolutely, exponentially more than my wife would embark upon to resolve her problem.