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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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

J
  • Locked
  • Question Question
LET/VSTACK/FILTER question
Replies
6
Views
368
Fluff
Fluff
S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
219
SBExcel123
S
L
  • Solved
Need A Help With My formula
Replies
3
Views
212
Cubist
Cubist
L
  • Solved
Send specific data from one sheet to another when a condition is met,
Replies
7
Views
274
mumps
mumps
C
  • Question Question
Aggregate function assistance or some other function?
Replies
2
Views
574
cspengel
C

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

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