Hello!
In my table, I'm doing lots of calculations. I want to highlight all cells that contain a formula with reference to any other cell.
E.g. my cells might have contents like this:
Formula Highlighting =$A$1+$A$2 Yes =ISTEXT((C25)) Yes 210 No =210 No =210+A5 Yes =210+35 No
I tried so many approaches to analyze the formulatext, but I failed again and again.
So maybe you can help with some good idea?
Thank you!
Max
Lets say your formulas you listed above are populated in cells E1:E6:
Conditional Formatting Formula:
Formula:
Please Login or Register to view this content.
<----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.
Hi dosydos!
Thank you very much, that's a very nice approach! And it might be good enough for my usecase
But I guess it is not perfect, since functions would be highlighted wrongly as well. For example
Your solution would highlight that cell, even though there is no reference to another cell.
Formula Highlighting =FLOOR(210.15) No
Hi Max,
You are correct, i hadn't considered that. This is the only other solution i could come up with. The only issue with this formula is that Excel functions that have a Letter followed by a number would be highlighted incorrectly.
an example of a function i am referring to is =DAYS360() or =LOG10(). The conditional formatting would treat that as a cell reference to cell S3 and G1 respectively.
Now there are very few functions in excel that use numbers in their name, and they are pretty uncommon unless you are in the engineering or statistical industry.
As for the included formula. This will work with cell references and absolute cell references.
I could not think of another way of showing that a letter and number combination is = to a cell reference other than the below formula which makes a list 468 in length concatenating A-Z,1-9, and $-"blank".
Conditional Formatting Formula entered same way as my original answer:
Formula:Please Login or Register to view this content.
Last edited by dosydos; 03-26-2024 at 02:48 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks