Copying formula

G

Greymud

New Member
Joined
Feb 19, 2016
Messages
42
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
EXCEL v 2016

I have 3 cells with % failure types (the three add up to 100%). The client asked for formatting of

CxxBxxAxx
where xx is 2 (or 3 if 100%) digit % for that failure type

If no failure is recorded, all 3 cells have '*' in them and need to be reported as '*'
0 needs to be reported as 00
Each % is rounded to nearest 10%

The formula I have is

=IF(Reporting!C48="*","*","C"&IF(Reporting!C50=0,"00",Reporting!C50)&"B"&IF(Reporting!C49=0,"00",Reporting!C49)&"A"&IF(Reporting!C48=0,"00",Reporting!C48))

It works fine, but the issue is I have 4 columns (4 samples) of 8 tests per sample. If I copy the formula, it doesn't fill correctly. I don't want to write each cell if I can avoid it. By dragging the cell handle, I get this in the next row

=IF(Reporting!C49="*","*","C"&IF(Reporting!C51=0,"00",Reporting!C51)&"B"&IF(Reporting!C50=0,"00",Reporting!C50)&"A"&IF(Reporting!C49=0,"00",Reporting!C49))

Data is in Reporting!C48:Reporting!F71. Each column is the % failures for 8 tests each of 4 samples.

Where I'm working


MrExcelSample.xlsx
E
8%
9C100B00A00
10C00B100A00
11C00B00A100
12C100B00A00
13C00B100A00
14C00B00A100
15C100B00A00
16C00B100A00
PDF
Cell Formulas
RangeFormula
E9:E16E9=IF(Reporting!C48="*","*","C"&IF(Reporting!C50=0,"00",Reporting!C50)&"B"&IF(Reporting!C49=0,"00",Reporting!C49)&"A"&IF(Reporting!C48=0,"00",Reporting!C48))


Data Set

MrExcelSample.xlsx
CDEF
480010040
4900040
50100100020
510*00
520*00
53100*100100
5400**
5500**
56100100**
570010025
5800025
59100100050
600*030
610*030
62100*10040
6310*00
640*00
6590*100100
6615*5010
670*010
6885*5080
6920152020
7000200
7180856080
Reporting
Cell Formulas
RangeFormula
C48C48=VLOOKUP($A$2,Adhesion!$A:$ED,5,FALSE)
D48D48=VLOOKUP($A$2,Adhesion!$A:$ED,38,FALSE)
E48E48=VLOOKUP($A$2,Adhesion!$A:$ED,71,FALSE)
F48F48=VLOOKUP($A$2,Adhesion!$A:$ED,104,FALSE)
C49C49=VLOOKUP($A$2,Adhesion!$A:$ED,6,FALSE)
D49D49=VLOOKUP($A$2,Adhesion!$A:$ED,39,FALSE)
E49E49=VLOOKUP($A$2,Adhesion!$A:$ED,72,FALSE)
F49F49=VLOOKUP($A$2,Adhesion!$A:$ED,105,FALSE)
C50C50=VLOOKUP($A$2,Adhesion!$A:$ED,7,FALSE)
D50D50=VLOOKUP($A$2,Adhesion!$A:$ED,40,FALSE)
E50E50=VLOOKUP($A$2,Adhesion!$A:$ED,73,FALSE)
F50F50=VLOOKUP($A$2,Adhesion!$A:$ED,106,FALSE)
C51C51=VLOOKUP($A$2,Adhesion!$A:$ED,9,FALSE)
D51D51=VLOOKUP($A$2,Adhesion!$A:$ED,42,FALSE)
E51E51=VLOOKUP($A$2,Adhesion!$A:$ED,75,FALSE)
F51F51=VLOOKUP($A$2,Adhesion!$A:$ED,108,FALSE)
C52C52=VLOOKUP($A$2,Adhesion!$A:$ED,10,FALSE)
D52D52=VLOOKUP($A$2,Adhesion!$A:$ED,43,FALSE)
E52E52=VLOOKUP($A$2,Adhesion!$A:$ED,76,FALSE)
F52F52=VLOOKUP($A$2,Adhesion!$A:$ED,109,FALSE)
C53C53=VLOOKUP($A$2,Adhesion!$A:$ED,11,FALSE)
D53D53=VLOOKUP($A$2,Adhesion!$A:$ED,44,FALSE)
E53E53=VLOOKUP($A$2,Adhesion!$A:$ED,77,FALSE)
F53F53=VLOOKUP($A$2,Adhesion!$A:$ED,110,FALSE)
C54C54=VLOOKUP($A$2,Adhesion!$A:$ED,13,FALSE)
D54D54=VLOOKUP($A$2,Adhesion!$A:$ED,46,FALSE)
E54E54=VLOOKUP($A$2,Adhesion!$A:$ED,79,FALSE)
F54F54=VLOOKUP($A$2,Adhesion!$A:$ED,112,FALSE)
C55C55=VLOOKUP($A$2,Adhesion!$A:$ED,14,FALSE)
D55D55=VLOOKUP($A$2,Adhesion!$A:$ED,47,FALSE)
E55E55=VLOOKUP($A$2,Adhesion!$A:$ED,80,FALSE)
F55F55=VLOOKUP($A$2,Adhesion!$A:$ED,113,FALSE)
C56C56=VLOOKUP($A$2,Adhesion!$A:$ED,15,FALSE)
D56D56=VLOOKUP($A$2,Adhesion!$A:$ED,48,FALSE)
E56E56=VLOOKUP($A$2,Adhesion!$A:$ED,81,FALSE)
F56F56=VLOOKUP($A$2,Adhesion!$A:$ED,114,FALSE)
C57C57=VLOOKUP($A$2,Adhesion!$A:$ED,17,FALSE)
D57D57=VLOOKUP($A$2,Adhesion!$A:$ED,50,FALSE)
E57E57=VLOOKUP($A$2,Adhesion!$A:$ED,83,FALSE)
F57F57=VLOOKUP($A$2,Adhesion!$A:$ED,116,FALSE)
C58C58=VLOOKUP($A$2,Adhesion!$A:$ED,18,FALSE)
D58D58=VLOOKUP($A$2,Adhesion!$A:$ED,51,FALSE)
E58E58=VLOOKUP($A$2,Adhesion!$A:$ED,84,FALSE)
F58F58=VLOOKUP($A$2,Adhesion!$A:$ED,117,FALSE)
C59C59=VLOOKUP($A$2,Adhesion!$A:$ED,19,FALSE)
D59D59=VLOOKUP($A$2,Adhesion!$A:$ED,52,FALSE)
E59E59=VLOOKUP($A$2,Adhesion!$A:$ED,85,FALSE)
F59F59=VLOOKUP($A$2,Adhesion!$A:$ED,118,FALSE)
C60C60=VLOOKUP($A$2,Adhesion!$A:$ED,21,FALSE)
D60D60=VLOOKUP($A$2,Adhesion!$A:$ED,54,FALSE)
E60E60=VLOOKUP($A$2,Adhesion!$A:$ED,87,FALSE)
F60F60=VLOOKUP($A$2,Adhesion!$A:$ED,120,FALSE)
C61C61=VLOOKUP($A$2,Adhesion!$A:$ED,22,FALSE)
D61D61=VLOOKUP($A$2,Adhesion!$A:$ED,55,FALSE)
E61E61=VLOOKUP($A$2,Adhesion!$A:$ED,88,FALSE)
F61F61=VLOOKUP($A$2,Adhesion!$A:$ED,121,FALSE)
C62C62=VLOOKUP($A$2,Adhesion!$A:$ED,23,FALSE)
D62D62=VLOOKUP($A$2,Adhesion!$A:$ED,56,FALSE)
E62E62=VLOOKUP($A$2,Adhesion!$A:$ED,89,FALSE)
F62F62=VLOOKUP($A$2,Adhesion!$A:$ED,122,FALSE)
C63C63=VLOOKUP($A$2,Adhesion!$A:$ED,25,FALSE)
D63D63=VLOOKUP($A$2,Adhesion!$A:$ED,58,FALSE)
E63E63=VLOOKUP($A$2,Adhesion!$A:$ED,91,FALSE)
F63F63=VLOOKUP($A$2,Adhesion!$A:$ED,124,FALSE)
C64C64=VLOOKUP($A$2,Adhesion!$A:$ED,26,FALSE)
D64D64=VLOOKUP($A$2,Adhesion!$A:$ED,59,FALSE)
E64E64=VLOOKUP($A$2,Adhesion!$A:$ED,92,FALSE)
F64F64=VLOOKUP($A$2,Adhesion!$A:$ED,125,FALSE)
C65C65=VLOOKUP($A$2,Adhesion!$A:$ED,27,FALSE)
D65D65=VLOOKUP($A$2,Adhesion!$A:$ED,60,FALSE)
E65E65=VLOOKUP($A$2,Adhesion!$A:$ED,93,FALSE)
F65F65=VLOOKUP($A$2,Adhesion!$A:$ED,126,FALSE)
C66C66=VLOOKUP($A$2,Adhesion!$A:$ED,29,FALSE)
D66D66=VLOOKUP($A$2,Adhesion!$A:$ED,62,FALSE)
E66E66=VLOOKUP($A$2,Adhesion!$A:$ED,95,FALSE)
F66F66=VLOOKUP($A$2,Adhesion!$A:$ED,128,FALSE)
C67C67=VLOOKUP($A$2,Adhesion!$A:$ED,30,FALSE)
D67D67=VLOOKUP($A$2,Adhesion!$A:$ED,63,FALSE)
E67E67=VLOOKUP($A$2,Adhesion!$A:$ED,96,FALSE)
F67F67=VLOOKUP($A$2,Adhesion!$A:$ED,129,FALSE)
C68C68=VLOOKUP($A$2,Adhesion!$A:$ED,31,FALSE)
D68D68=VLOOKUP($A$2,Adhesion!$A:$ED,64,FALSE)
E68E68=VLOOKUP($A$2,Adhesion!$A:$ED,97,FALSE)
F68F68=VLOOKUP($A$2,Adhesion!$A:$ED,130,FALSE)
C69C69=VLOOKUP($A$2,Adhesion!$A:$ED,33,FALSE)
D69D69=VLOOKUP($A$2,Adhesion!$A:$ED,66,FALSE)
E69E69=VLOOKUP($A$2,Adhesion!$A:$ED,99,FALSE)
F69F69=VLOOKUP($A$2,Adhesion!$A:$ED,132,FALSE)
C70C70=VLOOKUP($A$2,Adhesion!$A:$ED,34,FALSE)
D70D70=VLOOKUP($A$2,Adhesion!$A:$ED,67,FALSE)
E70E70=VLOOKUP($A$2,Adhesion!$A:$ED,100,FALSE)
F70F70=VLOOKUP($A$2,Adhesion!$A:$ED,133,FALSE)
C71C71=VLOOKUP($A$2,Adhesion!$A:$ED,35,FALSE)
D71D71=VLOOKUP($A$2,Adhesion!$A:$ED,68,FALSE)
E71E71=VLOOKUP($A$2,Adhesion!$A:$ED,101,FALSE)
F71F71=VLOOKUP($A$2,Adhesion!$A:$ED,134,FALSE)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F69Expression=(F69+F70+F71)<>100textNO
F66Expression=(F66+F67+F68)<>100textNO
F63Expression=(F63+F64+F65)<>100textNO
F60Expression=(F60+F61+F62)<>100textNO
F57Expression=(F57+F58+F59)<>100textNO
F54Expression=(F54+F55+F56)<>100textNO
F51Expression=(F51+F52+F53)<>100textNO
E69Expression=(E69+E70+E71)<>100textNO
E66Expression=(E66+E67+E68)<>100textNO
E63Expression=(E63+E64+E65)<>100textNO
E60Expression=(E60+E61+E62)<>100textNO
E57Expression=(E57+E58+E59)<>100textNO
E54Expression=(E54+E55+E56)<>100textNO
E51Expression=(E51+E52+E53)<>100textNO
D69Expression=(D69+D70+D71)<>100textNO
D66Expression=(D66+D67+D68)<>100textNO
D63Expression=(D63+D64+D65)<>100textNO
D60Expression=(D60+D61+D62)<>100textNO
D57Expression=(D57+D58+D59)<>100textNO
D54Expression=(D54+D55+D56)<>100textNO
D51Expression=(D51+D52+D53)<>100textNO
C69Expression=(C69+C70+C71)<>100textNO
C66Expression=(C66+C67+C68)<>100textNO
C63Expression=(C63+C64+C65)<>100textNO
C60Expression=(C60+C61+C62)<>100textNO
C57Expression=(C57+C58+C59)<>100textNO
C54Expression=(C54+C55+C56)<>100textNO
C51Expression=(C51+C52+C53)<>100textNO
F48Expression=(F48+F49+F50)<>100textNO
E48Expression=(E48+E49+E50)<>100textNO
D48Expression=(D48+D49+D50)<>100textNO
C48Expression=(C48+C49+C50)<>100textNO
 
Last edited:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Hi, see the linked files (with 6/3 auxiliary columns) for a possible solution...

The formulas used in the CopyFormulaV1 table:
A10 =IF(LEFT(A9,1)="F","C"&RIGHT(A9,2)+3,CHAR(CODE(LEFT(A9,1))+1)&RIGHT(A9,2))
B10 =IF(LEFT(B9,1)="F","C"&RIGHT(B9,2)+3,CHAR(CODE(LEFT(B9,1))+1)&RIGHT(B9,2))
C10 =IF(LEFT(C9,1)="F","C"&RIGHT(C9,2)+3,CHAR(CODE(LEFT(C9,1))+1)&RIGHT(C9,2))
D9 =INDEX(INDEX(Reporting!$1:$1048576,,CODE(LEFT(A9,1))-64),RIGHT(A9,2))
E9 =INDEX(INDEX(Reporting!$1:$1048576,,CODE(LEFT(B9,1))-64),RIGHT(B9,2))
F9 =INDEX(INDEX(Reporting!$1:$1048576,,CODE(LEFT(C9,1))-64),RIGHT(C9,2))
G9 =IF(D9="*","*","C"&IF(F9=0,"00",F9)&"B"&IF(E9=0,"00",E9)&"A"&IF(D9=0,"00",D9))

The formulas used in the CopyFormulaV2 table:
A10 =IF(LEFT(A9,1)="F","C"&RIGHT(A9,2)+3,CHAR(CODE(LEFT(A9,1))+1)&RIGHT(A9,2))
B10 =IF(LEFT(B9,1)="F","C"&RIGHT(B9,2)+3,CHAR(CODE(LEFT(B9,1))+1)&RIGHT(B9,2))
C10 =IF(LEFT(C9,1)="F","C"&RIGHT(C9,2)+3,CHAR(CODE(LEFT(C9,1))+1)&RIGHT(C9,2))
E9 =IF(INDEX(INDEX(Reporting!$1:$1048576,,CODE(LEFT(A9,1))-64),RIGHT(A9,2))="*","*","C"&IF(INDEX(INDEX(Reporting!$1:$1048576,,CODE(LEFT(C9,1))-64),RIGHT(C9,2))=0,"00",INDEX(INDEX(Reporting!$1:$1048576,,CODE(LEFT(C9,1))-64),RIGHT(C9,2)))&"B"&IF(INDEX(INDEX(Reporting!$1:$1048576,,CODE(LEFT(B9,1))-64),RIGHT(B9,2))=0,"00",INDEX(INDEX(Reporting!$1:$1048576,,CODE(LEFT(B9,1))-64),RIGHT(B9,2)))&"A"&IF(INDEX(INDEX(Reporting!$1:$1048576,,CODE(LEFT(A9,1))-64),RIGHT(A9,2))=0,"00",INDEX(INDEX(Reporting!$1:$1048576,,CODE(LEFT(A9,1))-64),RIGHT(A9,2))))

CopyFormulaV1.xlsx
CopyFormulaV2.xlsx
 
Upvote 0
Solution
fjns said:
Hi, see the linked files (with 6/3 auxiliary columns) for a possible solution...

The formulas used in the CopyFormulaV1 table:
A10 =IF(LEFT(A9,1)="F","C"&RIGHT(A9,2)+3,CHAR(CODE(LEFT(A9,1))+1)&RIGHT(A9,2))
B10 =IF(LEFT(B9,1)="F","C"&RIGHT(B9,2)+3,CHAR(CODE(LEFT(B9,1))+1)&RIGHT(B9,2))
C10 =IF(LEFT(C9,1)="F","C"&RIGHT(C9,2)+3,CHAR(CODE(LEFT(C9,1))+1)&RIGHT(C9,2))
D9 =INDEX(INDEX(Reporting!$1:$1048576,,CODE(LEFT(A9,1))-64),RIGHT(A9,2))
E9 =INDEX(INDEX(Reporting!$1:$1048576,,CODE(LEFT(B9,1))-64),RIGHT(B9,2))
F9 =INDEX(INDEX(Reporting!$1:$1048576,,CODE(LEFT(C9,1))-64),RIGHT(C9,2))
G9 =IF(D9="*","*","C"&IF(F9=0,"00",F9)&"B"&IF(E9=0,"00",E9)&"A"&IF(D9=0,"00",D9))

The formulas used in the CopyFormulaV2 table:
A10 =IF(LEFT(A9,1)="F","C"&RIGHT(A9,2)+3,CHAR(CODE(LEFT(A9,1))+1)&RIGHT(A9,2))
B10 =IF(LEFT(B9,1)="F","C"&RIGHT(B9,2)+3,CHAR(CODE(LEFT(B9,1))+1)&RIGHT(B9,2))
C10 =IF(LEFT(C9,1)="F","C"&RIGHT(C9,2)+3,CHAR(CODE(LEFT(C9,1))+1)&RIGHT(C9,2))
E9 =IF(INDEX(INDEX(Reporting!$1:$1048576,,CODE(LEFT(A9,1))-64),RIGHT(A9,2))="*","*","C"&IF(INDEX(INDEX(Reporting!$1:$1048576,,CODE(LEFT(C9,1))-64),RIGHT(C9,2))=0,"00",INDEX(INDEX(Reporting!$1:$1048576,,CODE(LEFT(C9,1))-64),RIGHT(C9,2)))&"B"&IF(INDEX(INDEX(Reporting!$1:$1048576,,CODE(LEFT(B9,1))-64),RIGHT(B9,2))=0,"00",INDEX(INDEX(Reporting!$1:$1048576,,CODE(LEFT(B9,1))-64),RIGHT(B9,2)))&"A"&IF(INDEX(INDEX(Reporting!$1:$1048576,,CODE(LEFT(A9,1))-64),RIGHT(A9,2))=0,"00",INDEX(INDEX(Reporting!$1:$1048576,,CODE(LEFT(A9,1))-64),RIGHT(A9,2))))

CopyFormulaV1.xlsx
CopyFormulaV2.xlsx
Click to expand...
Thanks! I think I can make a variation of this work - once I decipher a few of these cells.
 
Upvote 0
Hi, I am very glad that the formula works.
If I have enough knowledge, I am happy to help at any time.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Aggregate function assistance or some other function?
Replies
2
Views
460
cspengel
C
G
  • Question
Copy Range of Cells based on Criteria, Paste it on rows below it, Do This Until All conditions are met, Then Delete All Copied Rows
Replies
0
Views
211
glaccounting
G
A
  • Question
WEIRD CODE: Work on Windows but does not work on Mac. The error on mac is Named argument already specified
Replies
5
Views
411
RoryA
RoryA
V
  • Solved
Progressive average formula that counts also blank cells
2
Replies
17
Views
451
kvsrinivasamurthy
K
H
  • Solved
Numerating the cells by 2 criteria
Replies
3
Views
295
FormR
FormR

Forum statistics

Threads
1,214,657
Messages
6,120,773
Members
448,991
Latest member
Hanakoro

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top