Forum Discussion
sunnyschindler
Jul 25, 2023Brass Contributor
LOOKUP to include formatting with its Return Value? Formatting Code Function? non-VBA request
Without VBA, is it possible for LOOKUP to return formatting of its Return Value? It is much easier if Formatting Code Function (similar to CODE function) can be exposed to end user. I need to di...
bosinander
Jul 26, 2023Steel Contributor
sunnyschindler If your formatting needs are limited to find out which part of a long formula has returned the result, it may be possible to convert the lookup result to text. ie, if the result is to be numeric. Apply TEXT function around LOOKUP.
You may then switch between "developer mode" to user mode by right adjusting the result column and have all values look the same but if you need to use the result as input for further calculations, numbers as text will give you bad results.
This is a possibility but I do not recommend it because of possible calculation erorrs further on.
I'ld rather use a cell with 1 or 0 to switch between visualising what formula part the result comes from.
Above usable in classic Excel vesions.
If 365 the formula may handle both alfa and numeric replies without making the formula even longer by repeating the lookup part (formula row 3)
If the need is for the end user to know, then I would recommend another approach by putting the lookup part in an own hidden column. Then you could also use conditional formatting and have most of the formatting possibilities.
It's also possible to use positive and negative results but _showing_ them as positives with some possibilities to switch font color if found by lookup (=negative numbers).
Here, the numbers are formatted as 0;[Color5]0 and color5 looks like blue.
More about Excel colors eg at http://dmcritchie.mvps.org/excel/colors.htm
- sunnyschindlerJul 27, 2023Brass ContributorThanks, I learnt more today but should have mentioned that my need is to mobile text a string; by TEXTJOIN LOOKUP outputs.
The best I can come up with (w/o VBA) paste TEXTJOIN output into WORD APP to substitute "control characters" (which, similar to your suggestion, wrapped around each LOOUP output) with superscripts and subscripts depending according to "control characters". Appreciate your help!- peiyezhuJul 27, 2023Bronze Contributormobile text a string;
what do you mean this?
can.you. upload a file rather than screenshot image and show your expected result?
how about html like below?
https://answers.microsoft.com/en-us/msoffice/forum/all/how-do-i-highlight-cells-based-on-data-from/aeae4552-08f5-4742-92d6-4885298fa5bf