Oct 20 2022 06:17 AM - edited Oct 20 2022 07:20 AM
=IF(INDIRECT(ADRESS(ROW();COLUMN()))>1;1;0)
It doen't work, when I put it in the formula for conditional formatting, but for example when I try to separate it and use "INDIRECT(ADRESS(ROW();COLUMN()))>1" or simple "IF" statement like "=IF(ROW()>1;1;1)" (it isn't exactly what I want, it is just for demonstration)
UPD. The formula works fine in google sheets. I have troubles only using excel from microsoft 365.
Oct 20 2022 06:36 AM
What do you want to do? Highlight the active cell?
Oct 20 2022 06:40 AM
Oct 20 2022 06:56 AM
If you want to highlight cells whose value is is a number greater than 1:
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=AND(ISNUMBER(A2),A2>1)
Oct 20 2022 07:01 AM
Oct 20 2022 07:32 AM
If you want us to help you, you will have to tell us what you want - in detail.
Oct 20 2022 07:34 AM
Oct 20 2022 07:45 AM
It's because the formula evaluates to an error in Conditional Formatting.
So it'd be useful to know what you want to accomplish.
Oct 20 2022 07:52 AM
Oct 20 2022 10:46 AM
I don't know why it causes an error, but it does. In the Immediate window of the Visual Basic Editor:
Debug.Print Evaluate("=INDIRECT(ADRESS(ROW(),COLUMN()))")
Error 2029
Oct 20 2022 12:30 PM
This might work. I see you're using semi-colons so I've written the formula using those as the list separator. I believe the issue with the other formula is the use of ADDRESS.
=IF(INDIRECT("RC";0)>1;1;0)
Oct 20 2022 12:49 PM
But wouldn't be much easier to create a rule of type 'Format only cells that contain' with Cell Value, greater than, and =1 ?
Oct 20 2022 01:10 PM - edited Oct 20 2022 01:11 PM
Yes, definitely much easier. @captainFlow mentioned this was for demonstration purposes. Maybe he can share his goal?
Oct 20 2022 01:18 PM - edited Oct 20 2022 01:45 PM
Hi Patrick
Yes, you are right. Here is full formula I need.
=IF(INDIRECT(ADRESS(ROW();COLUMN())) > 0; IF((ROW()=1); INDIRECT(ADRESS(ROW();COLUMN())) >= ($D2*1,1); INDIRECT(ADRESS(ROW();COLUMN())) >= INDIRECT(ADRESS(ROW()-1;COLUMN()))*1,0); 0)
I created the tickets after found out that nested IF statement doesn't work for me.
UPD. To be more correct, using INDIRECT function inside of IF doesn't work for me.
UPD.2. The thing is I've written this formula for google sheets for Conditional Formatting and it works fine. I can't find out why it doesn't work for excel in microsoft365
Oct 20 2022 01:33 PM
@Hans Vogelaar I spent few minutes for searchin this error meaning but found nothing usefull. I use INDIRECT(ADRESS(ROW();COLUMN())) - only for getting current value during Conditional Formatting evaluations, so it seems complex but I don't know how to make it in a different way.
I also tried to use CELL("value") function, but it behaves strange, seems like it doesn't get each cell's value for each evaluation, but gets ONE value for all evaluations for Conditional Formatting within current range.
Oct 20 2022 01:56 PM
Your formula is syntactically incorrect, and I suspect it is far more complicated than necessary.
What is the range you want to format?
And what are the conditions?
Oct 20 2022 02:06 PM
@Hans Vogelaar
It may be incorrect after I translated in English, because I wrote it in Russian and I use Russian for all formulas I write because it it local language for my document. Notice, please, that this formula works fine when I put it as fomula into the cell. I face with troubles during putting it in the formula for Conditional Formatting.
Formula doesn't have accurate bounds, so I can't say the range. I want it will work for many colums. But this formula contain one absolute cell adress.
What do you means with conditions? What I want my formula does?
Oct 20 2022 02:35 PM
Yes: > What I want my formula does?
Oct 20 2022 02:45 PM
@Hans Vogelaar
It should return 0 if cell is empty.
If it is cell from the 1st row it should return 1 if cell's value is X percent more than the value in $D2 (for example) cell and 0 otherwise.
If it is cell from non the 1st row it should return 1 if value from the current cell is X percents more that value from the cell from the previous row but the same column.
Thanks for your help. I appreciate it.
Oct 20 2022 03:06 PM
Let's say you want to apply the conditional formatting to G1:K100.
Select this range. G1 should be the active cell in the selection.
Use the following formula in the conditional formatting rule:
=OR(G1="";IF(ROW(G1)=1;G1>=1,1*$D$2;OFFSET(G1;-1;0)>=1,1*$D$2))
=ИЛИ(G1="";ЕСЛИ(СТРОКА(G1)=1;G1>=1,1*$D$2;СМЕЩ(G1;-1;0)>=1,1*$D$2))
You may have to adjust the multiplication factor 1,1