ScorchedLemonade
Technical User
Hi,
I'm curious as to a way to do this in Excel:
suppose A1 and A2 hold the same value but are calculated in different ways, namely
A1 just has 5 entered
A2 has something like =max(B2,C2)-D2 where B2=7, C2=1 and D2=2
I want to be able to detect that A1 is not calculated using references to other cells (or, more generally, just that what was entered doesn't start with "=", as in =max(3,5), although I guess it's possible at some low level an "=" might be assumed even if only 5 is entered). B2, C2 and D2 would hold data feeding from a (checkable) external source. Sometimes the data might not be reliable - in this case the formula would be overwritten with the correct value (to give something of the type in A1). In this case, the cell would be made bold. This can be done manually (and unbolded later - when the data is reliable) but it would be preferable to have some kind of formula in a conditional format that would check and make the cell bold or unbold as appropriate.
Thus, preferably with something like a conditional format with a formula (rather than with VB code - I can do that, having mistakenly asked in the wrong forum and having obtained a VB solution - mainly because someone else in our company wrote the file and I don't want to just add extra code), it would make anything of the first type bold (and wouldn't format things of the second type). Unfortunately, my naive attempts at this - like =left(A2,1)="=" or =iserror(search("/",A2)+1), =len(A1) - do not work as hoped as they all look at the value in A2, rather than the formula used to calculate A2, and are hence unable to distinguish the 2 possibilities.
If anyone can suggest a solution, I'd be most grateful.
Thanks.
I'm curious as to a way to do this in Excel:
suppose A1 and A2 hold the same value but are calculated in different ways, namely
A1 just has 5 entered
A2 has something like =max(B2,C2)-D2 where B2=7, C2=1 and D2=2
I want to be able to detect that A1 is not calculated using references to other cells (or, more generally, just that what was entered doesn't start with "=", as in =max(3,5), although I guess it's possible at some low level an "=" might be assumed even if only 5 is entered). B2, C2 and D2 would hold data feeding from a (checkable) external source. Sometimes the data might not be reliable - in this case the formula would be overwritten with the correct value (to give something of the type in A1). In this case, the cell would be made bold. This can be done manually (and unbolded later - when the data is reliable) but it would be preferable to have some kind of formula in a conditional format that would check and make the cell bold or unbold as appropriate.
Thus, preferably with something like a conditional format with a formula (rather than with VB code - I can do that, having mistakenly asked in the wrong forum and having obtained a VB solution - mainly because someone else in our company wrote the file and I don't want to just add extra code), it would make anything of the first type bold (and wouldn't format things of the second type). Unfortunately, my naive attempts at this - like =left(A2,1)="=" or =iserror(search("/",A2)+1), =len(A1) - do not work as hoped as they all look at the value in A2, rather than the formula used to calculate A2, and are hence unable to distinguish the 2 possibilities.
If anyone can suggest a solution, I'd be most grateful.
Thanks.