All,
I have a particular problem which if possible I would like to solve without resorting to a large VBA scripts.
To explain.
I have two sheets in a SS, once contains data (approx 10000 lines) each line is conditionally formatted to add a background colour, and a number format (£ or % etc). The conditions are based on the content of 3 cells which are searched for on the second sheet. All formatting works correctly and gives me a resulting sheet with every cell formatted as I need it to be. The problem is the conditional formatting is very "Labour intensive" which results in a very slow to use spread sheet.
What I would like to do is take the correctly formatted sheet and Copy it to a new workbook, but I need it to retain all the formatting whilst breaking the link to the reference sheet.
I know I could re-write the thing and use VBA to do all the formatting without using "Conditional" and force the formats direct to each cell, but it seems a lot of extra effort if I can avoid it.
To be clearer I will try and demonstrate what I'm doing and want to achieve.
The RAW Data sheet contains something like this.
Ref_Number Business Division Test Month_1 Month_2 ........... Month_n
1 Gizmos ABC SALES% .80 .81 ........... .95
3 Blobs XYX STOCK 100 98 ........... 40
123 THINGS SSS SALES 2000 2300 .......... 300
The formatted data sheet shows
Ref_Number Business Division Test Month_1 Month_2 ........... Month_n
1 Gizmos ABC SALES% 80% 81% ........... 95%
3 Blobs XYX STOCK 100 98 ........... 40
123 THINGS SSS SALES £2,000 £1,300 .......... £300
My reference sheet contains
Ref_Number Business Division Test Month_1 Month_2 ........... Month_n
1 Gizmos ABC SALES% AMBER AMBER ........... GREEN
3 Blobs XYX STOCK GREEN GREEN ........... RED
3 Blobs AAA STOCK RED RED ............ RED
123 THINGS SSS SALES GREEN AMBER .......... RED
The conditional formatting uses the "Test" field to define the value type (e.g % £ etc) and uses a number of conditions to find the correct format to apply (.e.g. =AND(IFERROR(LEFT($F1,5),"Wrong")="Value",IFERROR(FIND("%",$F1),0)=0 then apply format masK £9,999).
The second format uses SUMPRODUCT to identify the correct RAG status to apply.
=OFFSET(RAG!$A$1,SUMPRODUCT(--(RAG!$A$1:$A$3000=$A1),--(RAG!$B$1:$B$3000=$B1),--(RAG!$D$1:$D$3000=$D1),--(RAG!$E$1:$E$3000=$E1),--(RAG!$F$1:$F$3000=LEFT($F1,5)),ROW(RAG!A$1:$G$3000)-1),MATCH(G$1,RAG!$A$1:$L$1,0)-1)="GREEN"
Where RAG is the reference table.
So as you can see the formatted data is heavily reliant on conditional formatting, which was not a problem with a few hundred items, but it's into the thousands now. The uses does not need the "Working" file just the results of formatting.
I hope I've made clear what I want to do and given enough information.
What I do know Simple copy paste will not work, and paste special doesn't cope either. I also found a solution using word but that's not desirable.
If it can't be done then so be it, and VBA will have to be used but I thought I'd ask first.
Thank you for all your assistance in advance.
I have a particular problem which if possible I would like to solve without resorting to a large VBA scripts.
To explain.
I have two sheets in a SS, once contains data (approx 10000 lines) each line is conditionally formatted to add a background colour, and a number format (£ or % etc). The conditions are based on the content of 3 cells which are searched for on the second sheet. All formatting works correctly and gives me a resulting sheet with every cell formatted as I need it to be. The problem is the conditional formatting is very "Labour intensive" which results in a very slow to use spread sheet.
What I would like to do is take the correctly formatted sheet and Copy it to a new workbook, but I need it to retain all the formatting whilst breaking the link to the reference sheet.
I know I could re-write the thing and use VBA to do all the formatting without using "Conditional" and force the formats direct to each cell, but it seems a lot of extra effort if I can avoid it.
To be clearer I will try and demonstrate what I'm doing and want to achieve.
The RAW Data sheet contains something like this.
Ref_Number Business Division Test Month_1 Month_2 ........... Month_n
1 Gizmos ABC SALES% .80 .81 ........... .95
3 Blobs XYX STOCK 100 98 ........... 40
123 THINGS SSS SALES 2000 2300 .......... 300
The formatted data sheet shows
Ref_Number Business Division Test Month_1 Month_2 ........... Month_n
1 Gizmos ABC SALES% 80% 81% ........... 95%
3 Blobs XYX STOCK 100 98 ........... 40
123 THINGS SSS SALES £2,000 £1,300 .......... £300
My reference sheet contains
Ref_Number Business Division Test Month_1 Month_2 ........... Month_n
1 Gizmos ABC SALES% AMBER AMBER ........... GREEN
3 Blobs XYX STOCK GREEN GREEN ........... RED
3 Blobs AAA STOCK RED RED ............ RED
123 THINGS SSS SALES GREEN AMBER .......... RED
The conditional formatting uses the "Test" field to define the value type (e.g % £ etc) and uses a number of conditions to find the correct format to apply (.e.g. =AND(IFERROR(LEFT($F1,5),"Wrong")="Value",IFERROR(FIND("%",$F1),0)=0 then apply format masK £9,999).
The second format uses SUMPRODUCT to identify the correct RAG status to apply.
=OFFSET(RAG!$A$1,SUMPRODUCT(--(RAG!$A$1:$A$3000=$A1),--(RAG!$B$1:$B$3000=$B1),--(RAG!$D$1:$D$3000=$D1),--(RAG!$E$1:$E$3000=$E1),--(RAG!$F$1:$F$3000=LEFT($F1,5)),ROW(RAG!A$1:$G$3000)-1),MATCH(G$1,RAG!$A$1:$L$1,0)-1)="GREEN"
Where RAG is the reference table.
So as you can see the formatted data is heavily reliant on conditional formatting, which was not a problem with a few hundred items, but it's into the thousands now. The uses does not need the "Working" file just the results of formatting.
I hope I've made clear what I want to do and given enough information.
What I do know Simple copy paste will not work, and paste special doesn't cope either. I also found a solution using word but that's not desirable.
If it can't be done then so be it, and VBA will have to be used but I thought I'd ask first.
Thank you for all your assistance in advance.
