How to detect formulas in cells (without VBA)
- Thread starter cyrilz
- Start date
You could use the UDF below (from http://www.vbaexpress.com/kb/getarticle.php?kb_id=62 ) using HasFormula as mentioned by VaraK:
[pre]
[/pre]
Assuming all of your formulae start with "=" you could then look for "=" in the first character.
[pre]
Code:
Function FTEXT(f As Range)
If f.HasFormula Then
FTEXT = f.Formula
Else: FTEXT = f
End If
End FunctionAssuming all of your formulae start with "=" you could then look for "=" in the first character.
@Hui : You're totally right.
Let me explain a bit about this :
My IT Guys install Office without allowing Macros by default, leading to the annoying and confusing secutity message.
They don't want to allow some directories to automatically accept macors (which I can undestand), so my users either :
- forget to allow the macro content
- deliberately close the message .
So VBA is not working.
I want to use a formula (already existing) in a conditional formating formula to display whether the task is done naturally or close by the manager by entering a 0 when the task needed less days than allowed.
Let me explain a bit about this :
My IT Guys install Office without allowing Macros by default, leading to the annoying and confusing secutity message.
They don't want to allow some directories to automatically accept macors (which I can undestand), so my users either :
- forget to allow the macro content
- deliberately close the message .
So VBA is not working.
I want to use a formula (already existing) in a conditional formating formula to display whether the task is done naturally or close by the manager by entering a 0 when the task needed less days than allowed.
Cyrilz,
Can you elaborate on "conditional formatting formula to display whether the task is done naturally or close by the manager by entering a 0 when the task needed less days than allowed"?
Do you have columns for Task days & Task Allowed ?
Do you have a field where a manager enters a 0 when a job is complete?
Where the manager enters a 0 is it zero before he enters it but calculated by a formula?
Can we use the above or other fields to determine the Conditional Formatting trigger?
Can you elaborate on "conditional formatting formula to display whether the task is done naturally or close by the manager by entering a 0 when the task needed less days than allowed"?
Do you have columns for Task days & Task Allowed ?
Do you have a field where a manager enters a 0 when a job is complete?
Where the manager enters a 0 is it zero before he enters it but calculated by a formula?
Can we use the above or other fields to determine the Conditional Formatting trigger?
Cyrilz
Despite you saying that your IT guys have disabled VBA, I don't think they have/can ?
If you goto VBA (Alt F11),
Right Click on your spreadsheet and Add Module and paste the following code in
[pre]
or as Conditional Formatting try =isformula(mycell)=TRUE[/code] where mycell is your cell ie: G6
You will have to save the file as a macro enabled worksheet
Despite you saying that your IT guys have disabled VBA, I don't think they have/can ?
If you goto VBA (Alt F11),
Right Click on your spreadsheet and Add Module and paste the following code in
[pre]
Code:
Private Function IsFormula(MyRange As Range)
IsFormula = False
If MyRange.HasFormula Then IsFormula = True
End Function[/pre]
Back on the spreadsheet try [code]=if(isformula,"Yes", "No")or as Conditional Formatting try =isformula(mycell)=TRUE[/code] where mycell is your cell ie: G6
You will have to save the file as a macro enabled worksheet
Cyrilz,
If I understand correctly you have a cell with a formula that gives 0 when a task is done, but sometimes the manager types a zero there if it is done before the formula says so?
If that is the case, maybe you can make the formula return a small number (0.01 for example) and format it to show just 1 decimal (0), then you can just test if it says 0 or 0.01 for your conditional format.
Or have the manager type "early" or something in a separate column and adjust your formula to output zero if that cell is filled.
If I understand correctly you have a cell with a formula that gives 0 when a task is done, but sometimes the manager types a zero there if it is done before the formula says so?
If that is the case, maybe you can make the formula return a small number (0.01 for example) and format it to show just 1 decimal (0), then you can just test if it says 0 or 0.01 for your conditional format.
Or have the manager type "early" or something in a separate column and adjust your formula to output zero if that cell is filled.
@Hui:
I have a column for Task allowed, and one for Task days remaining.
beside these 2 columns, I have a column for each week (see my submission next week) counting days remaining per week, each cell contains a formula like ={prevCellLeft} - {DaysSpentCell}, each week.
Task completion can be achieved by either :
- consuming all days left. This lead to 0 "by formula"
- deciding that task is complete, putting a 0 in the cell for the week. (thus removing the formula it contained).
I want to color differently each contition.
@Tessa
What you are saying inspires me that I can add a test against Spent vs. Allowed and see if they differ when the cell contains a 0 (either formula base or value).
Thanks you for sharing your thoughts.
------
BTW, I said that IT Guys installed Office without allowing for Macros (parameter is "ask what to do" when there are Macros and/or VBA).
My users are afraid of Macros and even more of VBA, because of the fact that Excel is familiar to them.
They think that they won't be able to update a XLS with functions and/or macros (they're obviously right).
That give them the feeling they are restrained from doing what they want to, so they reject quite often files with things beyond their understanding.
I have a column for Task allowed, and one for Task days remaining.
beside these 2 columns, I have a column for each week (see my submission next week) counting days remaining per week, each cell contains a formula like ={prevCellLeft} - {DaysSpentCell}, each week.
Task completion can be achieved by either :
- consuming all days left. This lead to 0 "by formula"
- deciding that task is complete, putting a 0 in the cell for the week. (thus removing the formula it contained).
I want to color differently each contition.
@Tessa
What you are saying inspires me that I can add a test against Spent vs. Allowed and see if they differ when the cell contains a 0 (either formula base or value).
Thanks you for sharing your thoughts.
------
BTW, I said that IT Guys installed Office without allowing for Macros (parameter is "ask what to do" when there are Macros and/or VBA).
My users are afraid of Macros and even more of VBA, because of the fact that Excel is familiar to them.
They think that they won't be able to update a XLS with functions and/or macros (they're obviously right).
That give them the feeling they are restrained from doing what they want to, so they reject quite often files with things beyond their understanding.
Cyrilz
So you can write 3 conditional formats
1. If Task Allowed > Task Remaining and Answer Not = 0
2. If Task Allowed = Task Remaining
3. If Task Allowed > Task Remaining and Answer =0)
which will give you what you are seeking
Assuming
Col B: Task Allowed
Col C: Task Remaining
Col D: Days Remaining
and for Row 10
In CF format
CF1: =and(B10>C10,D10<>0)=True
'Not yet complete
CF2: =B10=C10
'This is the formula 0
CF3: =and(B10>C10,D10=0)=True
'This is the User Entered 0
These will be applied in order above
Adjust Columns and rows to suit
So you can write 3 conditional formats
1. If Task Allowed > Task Remaining and Answer Not = 0
2. If Task Allowed = Task Remaining
3. If Task Allowed > Task Remaining and Answer =0)
which will give you what you are seeking
Assuming
Col B: Task Allowed
Col C: Task Remaining
Col D: Days Remaining
and for Row 10
In CF format
CF1: =and(B10>C10,D10<>0)=True
'Not yet complete
CF2: =B10=C10
'This is the formula 0
CF3: =and(B10>C10,D10=0)=True
'This is the User Entered 0
These will be applied in order above
Adjust Columns and rows to suit