Oct 02 2017 07:22 AM
Hello!
I have a spreadsheet with continously expanding rows. I want to create one column that has conditonal formatting with its corresponding adjacent cell. The format painter doesn't work because it will not allow for relative formatting, only absolute. The only solution I see is to manually enter the formula into each cell. This is not practical.
Is there and way to reuse the same formula relative to the cells being formatted?
Thank you.
Oct 02 2017 07:59 AM
Hi James,
If the rule is the same format painter shall work. For example, that is done by format painter:
Oct 02 2017 09:29 PM
Thank you. I appreciate your attempting to help me.
Let me better explain what I require:
In cell I3, i want to use the conditional format based on the value of J3.
I then want I4 to apply the same formula based on the value of J4. The same for I5 and J5, and so on.
I can use the format painter to apply the formula in the coulumn "I", but it will apply the formula only based on the value of J3.
I hope this provides more clairity of the situation, and I am grateful for the help. -James
Oct 03 2017 02:09 AM
Hi James,
I see. The workaround is to use offset, which gives the reference on next column and current row. Not sure what's your formula, could be like
=0.67*OFFSET($J$3,ROW()-3,0,1,1)
and apply it to your entire range. row()-3 returns first row in your range.
Oct 03 2017 02:18 AM
Forgot to say, with this you shall apply your rule only to first cell in the range (I3), aplly to next ones by format painter
Oct 03 2017 03:16 AM
Works great!! Thank you.
There will be an issue in the future, as this is going to be used as a continous log, when rows are added, that column will need to be "repainted" from the top cell, for the equation to be consistant. -But, that is another conversation, for another day.
Thank you very much!!
Oct 03 2017 03:27 AM
James,
You are welcome. When you are ready with new question better if you attach small sample file, will be easier to discuss.
Thank you
Nov 29 2018 06:29 PM
I spent the last 48 hours periodically trying to solve this problem and finding that same ineffective formula the original poster referenced, over and over. You appear the be the only person on Earth who knew how to do that, and thank you SO MUCH.
Nov 30 2018 04:16 AM
You are welcome. That's not my idea, if you google perhaps you find the post from the person who originally suggested the solution. But I lost the link and don't remember who that was.
Feb 13 2019 01:02 PM
I've attempted this solution but with the formula using $J$3 as an absolute reference in the Conditional Formatted formula, how can it be applied to the cells beneath such as J$4$, J$5$, etc?
For example, here is the range of the entire column I wish to format following the formula in cell P4.
In order to apply the same formatting to all cells in the same column, the formula would have to be relative such that the formula would change depending on the cell being formatted. However, I keep getting this error when attempting to use a relative reference.
How do I over come this challenge?
Feb 14 2019 04:25 PM
Sorry, I didn't catch which formula you try to use. Please check attached sample for Grade Scale.