Conditional Formatting to Drag
Solved
MelMez
Posted messages
14
Status
Membre
-
Vaucluse Posted messages 27336 Registration date Status Contributeur Last intervention -
Vaucluse Posted messages 27336 Registration date Status Contributeur Last intervention -
Hello everyone,
In my table with 4 columns (A to D), I want to apply conditional formatting starting from cell D and applicable to all cells (A to D), then drag it down through all the rows of my table so that:
Row 1: if D1<0 then A1:D1 turns white
Row 2: if D2<0 then A2:D2 turns white
and so on.
How can I drag my formatting down through all my rows (a total of 866)?
Thank you for your help.
PS: I cannot provide my file because my company's proxy prevents me from doing so.
Configuration: Windows 7 / Internet Explorer 8.0
In my table with 4 columns (A to D), I want to apply conditional formatting starting from cell D and applicable to all cells (A to D), then drag it down through all the rows of my table so that:
Row 1: if D1<0 then A1:D1 turns white
Row 2: if D2<0 then A2:D2 turns white
and so on.
How can I drag my formatting down through all my rows (a total of 866)?
Thank you for your help.
PS: I cannot provide my file because my company's proxy prevents me from doing so.
Configuration: Windows 7 / Internet Explorer 8.0
13 réponses
I use 2 methods:
_You drag and click on the small black square at the bottom right and then you click on the small icon that appears. "You choose to copy only the formatting"
_Home-Conditional Formatting-Manage Rules and you change the range of application
I hope I have helped you.
_You drag and click on the small black square at the bottom right and then you click on the small icon that appears. "You choose to copy only the formatting"
_Home-Conditional Formatting-Manage Rules and you change the range of application
I hope I have helped you.
Hello,
If I understood the request correctly, simply select your entire example table A1:D100 and for conditional formatting use the formula =$A1<0
choose the color format
A+
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
If I understood the request correctly, simply select your entire example table A1:D100 and for conditional formatting use the formula =$A1<0
choose the color format
A+
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
Re,
Well, I expressed myself poorly. I will try to provide a clearer example:
Col. A Col. B Col. C Col. D
L1 10/01/2022 11.67 1096.67 23.33
L2 11/01/2022 11.67 1108.33 11.67
L3 12/01/2022 11.67 1120.00 0.00
L4 01/01/2023 11.67 1131.67 -11.67
L5 02/01/2023 11.67 1143.33 -23.33
I want my formatting to highlight L4 (based on D4) and L5 (based on D5) in white. The other rows have amounts in Col. D greater than 0, so that’s good.
Knowing that all data is modifiable with each update of my table.
Is that clearer?
Well, I expressed myself poorly. I will try to provide a clearer example:
Col. A Col. B Col. C Col. D
L1 10/01/2022 11.67 1096.67 23.33
L2 11/01/2022 11.67 1108.33 11.67
L3 12/01/2022 11.67 1120.00 0.00
L4 01/01/2023 11.67 1131.67 -11.67
L5 02/01/2023 11.67 1143.33 -23.33
I want my formatting to highlight L4 (based on D4) and L5 (based on D5) in white. The other rows have amounts in Col. D greater than 0, so that’s good.
Knowing that all data is modifiable with each update of my table.
Is that clearer?
Re,
This is what I'm trying to do like this (with my example above):
in conditional formatting (using a formula...)
$DL5<0 (no IF because it's included in my formatting choice)
I choose the formatting in white
then "applies to" I select AL5:DL5
1. the formula doesn't work (well I don't understand anything anymore)
2. the "applies to": automatically puts $ which I don't want
3. when it works dragging with the cross or copying while keeping the formatting doesn't work
This is what I'm trying to do like this (with my example above):
in conditional formatting (using a formula...)
$DL5<0 (no IF because it's included in my formatting choice)
I choose the formatting in white
then "applies to" I select AL5:DL5
1. the formula doesn't work (well I don't understand anything anymore)
2. the "applies to": automatically puts $ which I don't want
3. when it works dragging with the cross or copying while keeping the formatting doesn't work
Re,
if you had followed my procedure your problem would be solved, let's start again
select all your table example A1:D5
conditional formatting, the formula is
=$D1<0
choose the fill color
for your new data, select AJ1:AL5 or AL1000 why not
and the formula will be if the values to test are in AL and your first value in AL1
the formula will be
=$AL1<0
--
A+
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
if you had followed my procedure your problem would be solved, let's start again
select all your table example A1:D5
conditional formatting, the formula is
=$D1<0
choose the fill color
for your new data, select AJ1:AL5 or AL1000 why not
and the formula will be if the values to test are in AL and your first value in AL1
the formula will be
=$AL1<0
--
A+
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
Re,
No, the formulas have nothing to do with it unless they return a text value; in that case, they need to be modified, but for that, we would need to see a part of your anonymized file that you can attach with this link
https://www.cjoint.com/
--
Cheers
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
No, the formulas have nothing to do with it unless they return a text value; in that case, they need to be modified, but for that, we would need to see a part of your anonymized file that you can attach with this link
https://www.cjoint.com/
--
Cheers
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
Re,
give an example, cell A12 what formula does it have
B12 what formula does it have
C12 what formula does it have
and D12 what formula does it have
--
A+
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
give an example, cell A12 what formula does it have
B12 what formula does it have
C12 what formula does it have
and D12 what formula does it have
--
A+
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
Here is the file in question (password Table)
https://sendbox.fr/index.php?/pro/rlvd8uhvukyr/Tableau_amortissements.xls.html
https://sendbox.fr/index.php?/pro/rlvd8uhvukyr/Tableau_amortissements.xls.html
Hi,
your link is unusable, click on the link below, then on browse to select your file/on create the link, just copy the link that will be generated at the top of the assistant into a post
https://www.cjoint.com/
--
See you later
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
your link is unusable, click on the link below, then on browse to select your file/on create the link, just copy the link that will be generated at the top of the assistant into a post
https://www.cjoint.com/
--
See you later
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
...oh? see here
https://www.cjoint.com/?DEnogazEorK
furthermore:
1°) are you sure about your formula in A12 that returns a date, but 0?
2°) is there a way to set up a table that stops displaying "to the last deadline" without using a conditional formatting?
Your option is dangerous, as the risk is to delete lines that we think are empty but are not.
best regards
https://www.cjoint.com/?DEnogazEorK
furthermore:
1°) are you sure about your formula in A12 that returns a date, but 0?
2°) is there a way to set up a table that stops displaying "to the last deadline" without using a conditional formatting?
Your option is dangerous, as the risk is to delete lines that we think are empty but are not.
best regards
Hi,
here it is simply
https://www.cjoint.com/?DEnoe0jfph5
reprogram the color I put in red so you can see
--
A+
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
here it is simply
https://www.cjoint.com/?DEnoe0jfph5
reprogram the color I put in red so you can see
--
A+
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
Well, thank you. I copied the formatting from your templates because it still wasn't working.
Vaucluse,
1) Yes, I'm sure, but the date is not entered in the table.
2) I'm open to the idea if I can learn and minimize risks. So, I'm going to protect the sheet to avoid any issues, and I know that my deadlines cannot go beyond line 131.
Vaucluse,
1) Yes, I'm sure, but the date is not entered in the table.
2) I'm open to the idea if I can learn and minimize risks. So, I'm going to protect the sheet to avoid any issues, and I know that my deadlines cannot go beyond line 131.
Re
here is one solution among others to adjust the schedule according to the number of years displayed in B3 of the Saisie sheet
https://www.cjoint.com/?DEnrhwYkQVN
The formulas remain up to row 131, and the conditional formatting is only used to shade the unused rows of the table.
For now, the formula starting from A13 refers to the number of years x 12, but it is certainly possible to modify it to indicate a specific number of months from a cell (in case the duration is not a complete number of years)
best regards
--
To err is human, to persist (in error) is diabolical
here is one solution among others to adjust the schedule according to the number of years displayed in B3 of the Saisie sheet
https://www.cjoint.com/?DEnrhwYkQVN
The formulas remain up to row 131, and the conditional formatting is only used to shade the unused rows of the table.
For now, the formula starting from A13 refers to the number of years x 12, but it is certainly possible to modify it to indicate a specific number of months from a cell (in case the duration is not a complete number of years)
best regards
--
To err is human, to persist (in error) is diabolical
Hello
In conditional formatting
Use a formula to determine which cells the format will be applied to Enter this formula
=IF($D1<0,1,0)
Then format
Fill white
Apply
Ok
And finally copy the cell then select all the cells and paste format
--
Practice makes perfect. - It's when you're up against the wall that you see the bricklayer - you always learn from your mistakes.
In conditional formatting
Use a formula to determine which cells the format will be applied to Enter this formula
=IF($D1<0,1,0)
Then format
Fill white
Apply
Ok
And finally copy the cell then select all the cells and paste format
--
Practice makes perfect. - It's when you're up against the wall that you see the bricklayer - you always learn from your mistakes.