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 04:22 PM
Oct 21 2022 01:23 AM
The reference to G1 in my example is relative. Excel will automatically adjust it for the other cells in the selected range. So when evaluating the conditional formatting rule for cell G2, it will use G2 in the formula instead of G2, and when evaluating the rule for cell H1, it will use H1 instead of G1, etc.
So you only have to refer to the top left cell of the selected range, and Excel will take care of the rest for you.
Oct 22 2022 09:38 AM
Oct 22 2022 10:02 AM
I'm afraid I don't know how to do that.
Oct 25 2022 02:44 AM
@Hans Vogelaar
One more thing that could help me is function like ROW or COLUMN, which returns value each cell during calculation. As I said before I found CELL function, but it doesn't work correctly for me.
Oct 25 2022 04:50 AM
I fear you're chasing rainbows...
Oct 25 2022 05:42 AM
Oct 25 2022 06:10 AM
Oct 28 2022 08:55 AM
@JMB17
Hi
Yes, it really works. I've tryed handle it with 'index' function, but couldn't finish it because of lack of experience. Big thanks :]
P.S. Getting current cell value via code seems little strange in that way XD;
Oct 28 2022 09:15 AM
@Patrick2788
Hi
Thanks for your reply, initially my function isn't supposed for using in big books, but thanks for yours warnings.
Oct 31 2022 06:28 AM
@Hans Vogelaar , @Patrick2788 Thanks for help, special thank to @JMB17 . This function really works for me, but one more thing you should think about is my formula became longer then 255 symbols, so I added function for getting current cell value to name manager via LAMBA function.