I have a question with regards to conditional formatting which I simply can't seem to solve.
The aim is to format the background color for the left-most cells that are blank up until a cell contains a value - and after this there should be no more formatting in this row. You can see an image of the result I'm hoping for beneath:
So far I've managed to create the conditional formatting of the blank rows, but sadly haven't managed to create the "single-cell" formatting (in yellow) conditional of the sum of all the first cells being = 0.
I've created a formula which actually succeeds in calculating the sum of the previous cells, but this formula includes INDIRECT() which it seems that conditional formatting doesn't allow. At least I'm getting an error starting with the follow (translating the error from Danish to English might not be intuitive):
You cannot use reference operators such as.....
The formula I'm trying is the following:
=AND(SUM(A2:INDIRECT(IF(COLUMN(A2)>=27;CHAR((64-26)+COLUMN(A2))&CHAR((64-26)+COLUMN(A2))&ROW(A2);CHAR((64)+COLUMN(A2))&ROW(A2))))>0;$K2>0)
Where $K2 is the sum of the row.
Is there a way to SUM a range of cells by doing something similar to this:
=SUM(B2:CHAR((64)+COLUMN())&ROW();"")) to dynamically SUM the range from B2 to the current cell? The problem in this case is whether the part CHAR((64)+COLUMN())&ROW();"") can be converted into a legible cell such as B4 to make it work inside the =SUM() formula?
Sadly the =ADDRESS() formula cannot be used (as far as I know) as this will trigger the same error in conditional formatting as well.
For now I would like this to work with the regular Excel conditional formatting, but if anyone have a simple VBA this would also have interest - however, I would by far prefer the regular solution.
The question: Is there a way to create a formula that doesn't trigger this error - maybe by refrasing the formula or doing something completely else?
