Copying conditional formatting between sheets for IMPORTRANGE

0
votes

https://docs.google.com/spreadsheets/d/1-f4cUjN5BB0jICErbVqpRA5oyumSLjlMS0xRI6U-PgQ/edit?usp=sharing

I'm trying to copy the data in this sheet from today's date to the dashboard. I have managed to do it but I would like to be able to do it with its conditioning.

Any ideas?

In the dashboard I basically just need to see whos data and what data has highlighted in red so needs action. The issues is that each column for each person has different conditional formatting based on standard deviation away from their means.

Any help or ideas would be greatly appreciated as I have been going for the last few days on this.

1
google-sheetsgs-conditional-formattingimportrange
Welcome. The linked spreadsheet has three sheets. Which one is "this sheet"? On the title, what do you mean by "for IMPORTRANGE"? Why is an issue that each column has different conditional formatting? - Rubén
Hi Ruben So what I want to do is copy the line of data for today’s date from each sheet onto the last sheet called dashboard. However it losses its conditional formatting it has in the original sheet. Import range allows be to copy the data from One sheet to the dashboard sheet with the filter for today’s date. Thanks Kath - Katherine Bester

1 Answers

0
votes

Despite the Title, some of the answers to How can I copy a conditional formatting from one document to another? appear to address copying within a document.

My main impression of your sheet is that you have far too many CF rules and that whatever you do about copying, it would be easier with fewer of them. I also do n't see the need for the hidden columns, 

=average(B4:B)

in B2 (your name would have to be moved - to A2?) and copied across to I2 would be sufficient for creating the CF rules in Sheet Kat Bester.

I think you need two sets of rules, one for where an increased number is deemed improvement and one for the opposite. So with the range B4:E300,G4:H300 (limiting this to the rows where you have dates):

Green: =B4>=B$2-stdeva(B$3:B$300)/2
Amber: =B4>=B$2-stdevA(B$4:B$300)
Red: =B4>B$2-stdeva(B$3:B$300)*2

and for F4:F300,I4:I300:

Red: =F4>F$2-stdeva(F$3:F$300)/2
Amber: =F4<=F$2-stdevA(F4:F300)
Green: =F4>F$2-stdevA(F$4:F$300)

The colour sequences matter.

This produces results different from what your sheet shows:

1) G5 : I think you are missing a $
2) 0 is not formatted
3) Empty cells are not formatted