I solved my earlier problem with conditional formatting but have bumped into another one: when I copy the conditional formatting to other cells the cell references in the custom formula don't update.
The rules I'm using in cell C7 are:
=NOT(isblank(C$3))
=((C7="y")+(C7="n"))
Is there any way to copy or apply these to other cells and have the references update?
I don't use Google Sheets, but in Excel you can set the range for application of the rule in the rule itself. Maybe you can in GS, too. It's more reliable than copying and pasting, as long as you get your absolute and relative references correct. Out of context, it's hard to say with just a rule and no worksheet.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Sorry, I should have said that I've tried applying the rule to a range. It applies the rule but the cell references remain static.
As an example: what I need is for:
=NOT(isblank(C$3))
=((C7="y")+(C7="n"))
to be updated to:
=NOT(isblank(D$3))
=((D8="y")+(D8="n"))
... when the formatting is copied from C7 to D8. Once this works I can apply it to the whole sheet that I want to develop.
Provide a link to a sample workbook.
As a newbie I'm not allowed to post links or images, and it seems I can't post attachments either.
Updated to say that I've sent the google sheet to myself and then attached it here ...
Last edited by Newbie53; 05-17-2023 at 11:03 AM. Reason: Correction
Post a link with TWO spaces in it. I will then FIX the link in your post for you.
It's better to see it on Google.
Maybe this will work ... remove the socks and replace the colon
https://docs.google.com/spreadsheets...it?usp=sharing
Edited to add: it did work!
Last edited by AliGW; 05-17-2023 at 02:07 PM.
Well, I’m sorry, but I askskedyou to put two spaces in it - nothing else. Unfortunately, therefore, I’ve been unable to fix it for you, so it’s if no use.
Please try again. Do not change the URL - just add two spaces to it.
Last edited by AliGW; 05-17-2023 at 01:14 PM.
I realise that you're trying to help, but deleting the pair of _socks_ and adding a colon should have been simple.
You said "Do not change the URL - just add two spaces to it" - I tried adding two spaces at various locations (including at the end) but the system always refuses to accept the post and tells me that I am not allowed to add links.
Please can you be specific about where to put the two spaces.
I think the link is working now.
Thanks!
Any suggestions on how to solve the problem?
I've had a look and I can see only two cells with letters in them in C and nothing in D. I am afraid I don't understand what you are trying to do.
Explain in WORDS what you want the rules to do and which cells they need applying to.
I want to be able to copy conditional formatting (that includes a custom formula) and to have the cell references within that formatting to be updated.
In other words the references within the conditional formatting rules need to be interpreted as relative references.
The desired function is for a cell to change colour when anything is entered into a second cell and for the first cell to revert to its original (white) colour if a Y or an N are entered into it.
The rules at the start of this discussion work as desired but I want to apply them to most of a worksheet so need the references to update.
You seem to have two separate rules. For this to work correctly, the rule would need to be rolled into one, I think.The desired function is for a cell to change colour when anything is entered into a second cell and for the first cell to revert to its original (white) colour if a Y or an N are entered into it.
I can't see where you have tried to copy the rule, nor can I see your having applied the rule to a range - just one cell.
Can you provide details of exactly where you want the rule copying and what the cells will or might contain so that I can check your findings? I am not clear which of the formula cells need to be relative: C$3 is column relative and row absolute, whereas C7 is completely relative. I am still not completely clear on the issue - sorry. You have to understand that whilst this is all completely obvious to you, it may not be to others who are trying to help.
When I asked a couple of days ago (I'm not allowed to post the link) I was told by another admin (6stringjazzer) that I could not combine the rules.
The rules as described provide the desired function but the references do not update when the conditional format is pasted into another cell.
When copying any conditional format which contains a custom formula to another cell you will see that the cell references do not change, despite not being marked as static references.
I can't explain this any more simply so try pasting a conditional format custom formula (say =isblank(A1)) into any cell (say A2), then copy it and past the conditional formatting into another cell (say B3) ... the cell reference in the custom formula will not have changed.
It's this that I'm trying to find a way around so that I can apply the conditional format to a large number of cells without having to manually alter all the cell references.
You are allowed to post links now as you have made 10 posts.
I'm sorry - I can't help any further. I don't use Google Sheets, so don't know enough about its conditional formatting function. I am pretty good with Excel's, but if you can't combine rules in GS then I am afraid I'm stumped. Hopefully someone esle will know.
Thanks, anyway.
What I told you is that you need a separate rule for each formatting style. You had described a situation where you had one rule for one color, and another rule for another color. Those cannot be combine into one rule. You can definitely combine rules into one formula if they have the same formatting style.Originally Posted by Newbie53
In Google Sheets and also Excel, a conditional formatting rule is written to apply to the upper-left cell in the applicable range. No matter what cell you look at, that rule will always refer to the same cell. Let's say you want a rule to turn the cell red if the value is negative. You want this rule to apply to the range A1:D4. You would use the formula
Formula:
Please Login or Register to view this content.
If you look at the rule for D4, it will still say
Formula:Please Login or Register to view this content.
You can apply this rule to whatever cells you want, the formula will not change, and you do not have to manually alter the cell reference.
I will try to review your thread and sheet and see if I can get down into the specifics as time permits.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks