Copying Data from Sheet 1 to Sheet 2 and adding rows- Code Needed

B

bamaisgreat

Well-known Member
Joined
Jan 23, 2012
Messages
831
Office Version
  1. 365
Platform
  1. Windows
Starting at A17 on Sheet 1 I have a row of data that I need the data from columns A,B,C,D and G (Column B is a specific ship date) copied to one of the ranges in sheet 2 that contain the 4 quaters of the year. The date in coulmn B determines which range of the quartely ranges it goes into. On sheet 2 the 4 quaters of the year 1st Quater Jan-March 2024 Range A4-Q4, 2nd Quarter April-June 2024 Range A6-Q6, 3rd Quarter July-Sep 2024 Range A8-Q8, 4th Quarter Oct-Dec 2024 Range A8-Q10. Once the data has been entered on sheet 1 I need it add another row with the same formatting as the row above it so the next line of data can be entered. On sheet 2 I also need a row added with the same formatting as the row above it to which ever quaterly ranges the data was copied into.
 
Sort by date Sort by votes
Im not sure what is happening but for some reason there are a few of the 2025 ship dates copying under the 4 quaters instead of all going in 2025. I did have to reformat the date column to show 4 digits for the year because when I had it as 2 digits and when I entered 25 excel would automatically change it 24. Below are the sheets that I re-entered the 2025 dates where you can see what happened. Beside that everything else seems to working as intended.

WORKING ON Franklin Job Review .xlsm
ABCDEFGHIJKLMNO
1FMI's Current Backlong of Projects
2ProjectShip DateCustomerJob Type Status% CompNotes CONTRACT PRICEInvoiced %AMOUNT BILLED TO DATEMargin ESTIMATED GROSS PROFITCOSTS TO DATEFMI Cost GG Cost
3Ret- Jobs
4F2048-RET2Allied Lock
5F2559AL-RET2
6F2612-RET5
7F2646-RET3
8F3105-RET4
9F3208-RET4
10F3348-RET5
11F3544-RET2
12F3616A-RET1
13
14
15F- Jobs
16ProjectShip DateCustomerJob Type Status% CompNotes CONTRACT PRICEInvoiced %AMOUNT BILLED TO DATEMargin ESTIMATED GROSS PROFITCOSTS TO DATEFMI Cost GG Cost
17F3685Dec-2023BigBeeF3685-PTW LineShipped / Not Closed Complete95%$0.00
18F3668Dec-2023AceroF3668-Material Handling Line Shipped / Not Closed Load containers next week50%
19F3669Apr-2024ACEROF3669-20" Flange LinesFab/ Machine50%$0.00
20F3670Feb-2024ACEROF3670-PTW LineIn Machine Shop50%$0.00
21F3697Jan-2024New MilleniumF3697-4330 Angle ShearIn Machine Shopbegin 35%$0.00
22F3699May-2024F3699- PTW 72FabReleased with F367035%$0.00
23F3700May-2024Standard StructuresF3700-Plasma System Fab35%$0.00
24F3701May-2024Standard StructuresF3701-Plate/Flange FabReleased with F369035%$0.00
25F3703Jan-2024CNH IndustriesF3703-Angle LineIn Machine Shop60%$0.00
26F3704Apr-2024Ideal SteelF3704-PTW Fab/ Machine35%$0.00
27F3705Feb-2024Chief BuildingsF3705- Plate/Flange LineFab/ Machine35%$0.00
28F3707Mar-2024ASTA DoorF3707-Angle Line with CoopersProgramming 35%$0.00
29F3710Nov-2024Lucas MetalsF3710-PTW 72Engineering35%$0.00
30F3711Nov-2024Lucas MetalsF3711-Plasma SystemEngineering35%$0.00
31F3712Nov-2024Lucas MetalsF3712-Plate/Flange Line Engineering35%$0.00
32F3713Dec-2024Valley JoistF3713-Valley Joist Angle LineEngineering35%$0.00
33F3714Dec-2024Vulcraft - TexasF3714-Long Span Angle LineEngineering35%$0.00
34F3715Jan-2025Central States F3715-Plasma LineEngineering35%$0.00
35F3716Jan-2025Central States F3716-PTW 72Engineering35%$0.00
36F3717Jan-2025Central States F3717-Plate/Flange Line Engineering35%$0.00
37F3718Aug-2024SiemansF3718-BusBarProgramming 20%$0.00
38F3719Aug-2024SiemansF3719-BusBarEngineering20%$0.00
39F3720Aug-2024SiemansF3720-BusBarEngineering20%$0.00
40F3721Aug-2024SiemansF3721-BusBarEngineering20%$0.00
41F3722Oct-2024SiemansF3722-BusBarEngineering20%$0.00
42F3723Oct-2024SiemansF3723-BusBarEngineering20%$0.00
43F3724Oct-2024SiemansF3724-BusBarEngineering20%$0.00
44F3725Mar-2024Bham Rail F3725 - Rail PressEngineering35%$0.00
45F3727Mar-2025ACI Buildings ExpansionF3727-72x240 slat table Engineering35%$0.00
46F3728Mar-2025ACI Buildings ExpansionF3728-Plate/Flange LineEngineering35%$0.00
47F3729Mar-2025ACI Buildings ExpansionF3729- PTW 72Engineering35%$0.00
48F3730Jan-2024SBS BuildingsF3730- Web seamersEngineering35%$0.00
49F3731Dec-2024AIG ControlsF3731- Shears / Nothers / PunchesEngineering$0.00
50
Sheet1
Cell Formulas
RangeFormula
J17,J19:J49J17=H17*I17


WORKING ON Franklin Job Review .xlsm
ABCDEFGHIJKLMNOPQ
1FMI's Current Backlong of Major Projects
2ProjectShip DateCustomerJob Type Status% CompNotes EngineeringPurchasing Machine ShopFab ShopAssembly
31st Quarter Jan-Mar 2024
4F3670Feb-2024ACEROF3670-PTW Line
5F3697Jan-2024New MilleniumF3697-4330 Angle Shearbegin
6F3703Jan-2024CNH IndustriesF3703-Angle Line
7F3705Feb-2024Chief BuildingsF3705- Plate/Flange Line
8F3707Mar-2024ASTA DoorF3707-Angle Line with Coopers
9F3716Jan-2025Central States F3716-PTW 72
10F3725Mar-2024Bham Rail F3725 - Rail Press
11F3730Jan-2024SBS BuildingsF3730- Web seamers
12
132nd Quarter April-June 2024
14F3669Apr-2024ACEROF3669-20" Flange Lines
15F3699May-2024F3699- PTW 72Released with F3670
16F3700May-2024Standard StructuresF3700-Plasma System
17F3701May-2024Standard StructuresF3701-Plate/Flange Released with F3690
18F3704Apr-2024Ideal SteelF3704-PTW
19
203rd Quarter July-Sep 2024
21F3718Aug-2024SiemansF3718-BusBar
22F3719Aug-2024SiemansF3719-BusBar
23F3720Aug-2024SiemansF3720-BusBar
24F3721Aug-2024SiemansF3721-BusBar
25
264th Quarter Oct-Dec 2024
27F3710Nov-2024Lucas MetalsF3710-PTW 72
28F3711Nov-2024Lucas MetalsF3711-Plasma System
29F3712Nov-2024Lucas MetalsF3712-Plate/Flange Line
30F3713Dec-2024Valley JoistF3713-Valley Joist Angle Line
31F3714Dec-2024Vulcraft - TexasF3714-Long Span Angle Line
32F3715Jan-2025Central States F3715-Plasma Line
33F3722Oct-2024SiemansF3722-BusBar
34F3723Oct-2024SiemansF3723-BusBar
35F3724Oct-2024SiemansF3724-BusBar
36F3731Dec-2024AIG ControlsF3731- Shears / Nothers / Punches
37
385th Quarter 2025
39F3717Jan-2025Central States F3717-Plate/Flange Line
40F3727Mar-2025ACI Buildings ExpansionF3727-72x240 slat table
41F3728Mar-2025ACI Buildings ExpansionF3728-Plate/Flange Line
42F3729Mar-2025ACI Buildings ExpansionF3729- PTW 72
43
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A6:Q100Expression=$A6<>""textNO
I12Other TypeColor scaleNO
I12Other TypeColor scaleNO
I12Other TypeColor scaleNO
I12Other TypeColor scaleNO
I12Other TypeColor scaleNO
I12Other TypeColor scaleNO
I12Other TypeColor scaleNO
I12Other TypeColor scaleNO
I12Other TypeColor scaleNO
I12Cell Value=1textNO
K12Other TypeColor scaleNO
K12Other TypeColor scaleNO
K12Other TypeColor scaleNO
K12Other TypeColor scaleNO
K12Other TypeColor scaleNO
K12Other TypeColor scaleNO
K12Other TypeColor scaleNO
K12Other TypeColor scaleNO
K12Other TypeColor scaleNO
K12Cell Value=1textNO
M12Other TypeColor scaleNO
M12Other TypeColor scaleNO
M12Other TypeColor scaleNO
M12Other TypeColor scaleNO
M12Other TypeColor scaleNO
M12Other TypeColor scaleNO
M12Other TypeColor scaleNO
M12Other TypeColor scaleNO
M12Other TypeColor scaleNO
M12Cell Value=1textNO
O12Other TypeColor scaleNO
O12Other TypeColor scaleNO
O12Other TypeColor scaleNO
O12Other TypeColor scaleNO
O12Other TypeColor scaleNO
O12Other TypeColor scaleNO
O12Other TypeColor scaleNO
O12Other TypeColor scaleNO
O12Other TypeColor scaleNO
O12Cell Value=1textNO
Q12Other TypeColor scaleNO
Q12Other TypeColor scaleNO
Q12Other TypeColor scaleNO
Q12Other TypeColor scaleNO
Q12Other TypeColor scaleNO
Q12Other TypeColor scaleNO
Q12Other TypeColor scaleNO
Q12Other TypeColor scaleNO
Q12Other TypeColor scaleNO
Q12Cell Value=1textNO
I37Other TypeColor scaleNO
I37Other TypeColor scaleNO
I37Other TypeColor scaleNO
I37Other TypeColor scaleNO
I37Other TypeColor scaleNO
I37Other TypeColor scaleNO
I37Other TypeColor scaleNO
I37Other TypeColor scaleNO
I37Other TypeColor scaleNO
I37Cell Value=1textNO
K37Other TypeColor scaleNO
K37Other TypeColor scaleNO
K37Other TypeColor scaleNO
K37Other TypeColor scaleNO
K37Other TypeColor scaleNO
K37Other TypeColor scaleNO
K37Other TypeColor scaleNO
K37Other TypeColor scaleNO
K37Other TypeColor scaleNO
K37Cell Value=1textNO
M37Other TypeColor scaleNO
M37Other TypeColor scaleNO
M37Other TypeColor scaleNO
M37Other TypeColor scaleNO
M37Other TypeColor scaleNO
M37Other TypeColor scaleNO
M37Other TypeColor scaleNO
M37Other TypeColor scaleNO
M37Other TypeColor scaleNO
M37Cell Value=1textNO
O37Other TypeColor scaleNO
O37Other TypeColor scaleNO
O37Other TypeColor scaleNO
O37Other TypeColor scaleNO
O37Other TypeColor scaleNO
O37Other TypeColor scaleNO
O37Other TypeColor scaleNO
O37Other TypeColor scaleNO
O37Other TypeColor scaleNO
O37Cell Value=1textNO
Q37Other TypeColor scaleNO
Q37Other TypeColor scaleNO
Q37Other TypeColor scaleNO
Q37Other TypeColor scaleNO
Q37Other TypeColor scaleNO
Q37Other TypeColor scaleNO
Q37Other TypeColor scaleNO
Q37Other TypeColor scaleNO
Q37Other TypeColor scaleNO
Q37Cell Value=1textNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Cell Value=1textNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Cell Value=1textNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Cell Value=1textNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Cell Value=1textNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Cell Value=1textNO
 
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Please disregard the post below. I saved and closed the workbook and when I opened it and re-entered the 2025 dates they went to the correct location.

bamaisgreat said:
Im not sure what is happening but for some reason there are a few of the 2025 ship dates copying under the 4 quaters instead of all going in 2025. I did have to reformat the date column to show 4 digits for the year because when I had it as 2 digits and when I entered 25 excel would automatically change it 24. Below are the sheets that I re-entered the 2025 dates where you can see what happened. Beside that everything else seems to working as intended.

WORKING ON Franklin Job Review .xlsm
ABCDEFGHIJKLMNO
1FMI's Current Backlong of Projects
2ProjectShip DateCustomerJob Type Status% CompNotes CONTRACT PRICEInvoiced %AMOUNT BILLED TO DATEMargin ESTIMATED GROSS PROFITCOSTS TO DATEFMI Cost GG Cost
3Ret- Jobs
4F2048-RET2Allied Lock
5F2559AL-RET2
6F2612-RET5
7F2646-RET3
8F3105-RET4
9F3208-RET4
10F3348-RET5
11F3544-RET2
12F3616A-RET1
13
14
15F- Jobs
16ProjectShip DateCustomerJob Type Status% CompNotes CONTRACT PRICEInvoiced %AMOUNT BILLED TO DATEMargin ESTIMATED GROSS PROFITCOSTS TO DATEFMI Cost GG Cost
17F3685Dec-2023BigBeeF3685-PTW LineShipped / Not Closed Complete95%$0.00
18F3668Dec-2023AceroF3668-Material Handling Line Shipped / Not Closed Load containers next week50%
19F3669Apr-2024ACEROF3669-20" Flange LinesFab/ Machine50%$0.00
20F3670Feb-2024ACEROF3670-PTW LineIn Machine Shop50%$0.00
21F3697Jan-2024New MilleniumF3697-4330 Angle ShearIn Machine Shopbegin 35%$0.00
22F3699May-2024F3699- PTW 72FabReleased with F367035%$0.00
23F3700May-2024Standard StructuresF3700-Plasma System Fab35%$0.00
24F3701May-2024Standard StructuresF3701-Plate/Flange FabReleased with F369035%$0.00
25F3703Jan-2024CNH IndustriesF3703-Angle LineIn Machine Shop60%$0.00
26F3704Apr-2024Ideal SteelF3704-PTW Fab/ Machine35%$0.00
27F3705Feb-2024Chief BuildingsF3705- Plate/Flange LineFab/ Machine35%$0.00
28F3707Mar-2024ASTA DoorF3707-Angle Line with CoopersProgramming 35%$0.00
29F3710Nov-2024Lucas MetalsF3710-PTW 72Engineering35%$0.00
30F3711Nov-2024Lucas MetalsF3711-Plasma SystemEngineering35%$0.00
31F3712Nov-2024Lucas MetalsF3712-Plate/Flange Line Engineering35%$0.00
32F3713Dec-2024Valley JoistF3713-Valley Joist Angle LineEngineering35%$0.00
33F3714Dec-2024Vulcraft - TexasF3714-Long Span Angle LineEngineering35%$0.00
34F3715Jan-2025Central States F3715-Plasma LineEngineering35%$0.00
35F3716Jan-2025Central States F3716-PTW 72Engineering35%$0.00
36F3717Jan-2025Central States F3717-Plate/Flange Line Engineering35%$0.00
37F3718Aug-2024SiemansF3718-BusBarProgramming 20%$0.00
38F3719Aug-2024SiemansF3719-BusBarEngineering20%$0.00
39F3720Aug-2024SiemansF3720-BusBarEngineering20%$0.00
40F3721Aug-2024SiemansF3721-BusBarEngineering20%$0.00
41F3722Oct-2024SiemansF3722-BusBarEngineering20%$0.00
42F3723Oct-2024SiemansF3723-BusBarEngineering20%$0.00
43F3724Oct-2024SiemansF3724-BusBarEngineering20%$0.00
44F3725Mar-2024Bham Rail F3725 - Rail PressEngineering35%$0.00
45F3727Mar-2025ACI Buildings ExpansionF3727-72x240 slat table Engineering35%$0.00
46F3728Mar-2025ACI Buildings ExpansionF3728-Plate/Flange LineEngineering35%$0.00
47F3729Mar-2025ACI Buildings ExpansionF3729- PTW 72Engineering35%$0.00
48F3730Jan-2024SBS BuildingsF3730- Web seamersEngineering35%$0.00
49F3731Dec-2024AIG ControlsF3731- Shears / Nothers / PunchesEngineering$0.00
50
Sheet1
Cell Formulas
RangeFormula
J17,J19:J49J17=H17*I17


WORKING ON Franklin Job Review .xlsm
ABCDEFGHIJKLMNOPQ
1FMI's Current Backlong of Major Projects
2ProjectShip DateCustomerJob Type Status% CompNotes EngineeringPurchasing Machine ShopFab ShopAssembly
31st Quarter Jan-Mar 2024
4F3670Feb-2024ACEROF3670-PTW Line
5F3697Jan-2024New MilleniumF3697-4330 Angle Shearbegin
6F3703Jan-2024CNH IndustriesF3703-Angle Line
7F3705Feb-2024Chief BuildingsF3705- Plate/Flange Line
8F3707Mar-2024ASTA DoorF3707-Angle Line with Coopers
9F3716Jan-2025Central States F3716-PTW 72
10F3725Mar-2024Bham Rail F3725 - Rail Press
11F3730Jan-2024SBS BuildingsF3730- Web seamers
12
132nd Quarter April-June 2024
14F3669Apr-2024ACEROF3669-20" Flange Lines
15F3699May-2024F3699- PTW 72Released with F3670
16F3700May-2024Standard StructuresF3700-Plasma System
17F3701May-2024Standard StructuresF3701-Plate/Flange Released with F3690
18F3704Apr-2024Ideal SteelF3704-PTW
19
203rd Quarter July-Sep 2024
21F3718Aug-2024SiemansF3718-BusBar
22F3719Aug-2024SiemansF3719-BusBar
23F3720Aug-2024SiemansF3720-BusBar
24F3721Aug-2024SiemansF3721-BusBar
25
264th Quarter Oct-Dec 2024
27F3710Nov-2024Lucas MetalsF3710-PTW 72
28F3711Nov-2024Lucas MetalsF3711-Plasma System
29F3712Nov-2024Lucas MetalsF3712-Plate/Flange Line
30F3713Dec-2024Valley JoistF3713-Valley Joist Angle Line
31F3714Dec-2024Vulcraft - TexasF3714-Long Span Angle Line
32F3715Jan-2025Central States F3715-Plasma Line
33F3722Oct-2024SiemansF3722-BusBar
34F3723Oct-2024SiemansF3723-BusBar
35F3724Oct-2024SiemansF3724-BusBar
36F3731Dec-2024AIG ControlsF3731- Shears / Nothers / Punches
37
385th Quarter 2025
39F3717Jan-2025Central States F3717-Plate/Flange Line
40F3727Mar-2025ACI Buildings ExpansionF3727-72x240 slat table
41F3728Mar-2025ACI Buildings ExpansionF3728-Plate/Flange Line
42F3729Mar-2025ACI Buildings ExpansionF3729- PTW 72
43
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A6:Q100Expression=$A6<>""textNO
I12Other TypeColor scaleNO
I12Other TypeColor scaleNO
I12Other TypeColor scaleNO
I12Other TypeColor scaleNO
I12Other TypeColor scaleNO
I12Other TypeColor scaleNO
I12Other TypeColor scaleNO
I12Other TypeColor scaleNO
I12Other TypeColor scaleNO
I12Cell Value=1textNO
K12Other TypeColor scaleNO
K12Other TypeColor scaleNO
K12Other TypeColor scaleNO
K12Other TypeColor scaleNO
K12Other TypeColor scaleNO
K12Other TypeColor scaleNO
K12Other TypeColor scaleNO
K12Other TypeColor scaleNO
K12Other TypeColor scaleNO
K12Cell Value=1textNO
M12Other TypeColor scaleNO
M12Other TypeColor scaleNO
M12Other TypeColor scaleNO
M12Other TypeColor scaleNO
M12Other TypeColor scaleNO
M12Other TypeColor scaleNO
M12Other TypeColor scaleNO
M12Other TypeColor scaleNO
M12Other TypeColor scaleNO
M12Cell Value=1textNO
O12Other TypeColor scaleNO
O12Other TypeColor scaleNO
O12Other TypeColor scaleNO
O12Other TypeColor scaleNO
O12Other TypeColor scaleNO
O12Other TypeColor scaleNO
O12Other TypeColor scaleNO
O12Other TypeColor scaleNO
O12Other TypeColor scaleNO
O12Cell Value=1textNO
Q12Other TypeColor scaleNO
Q12Other TypeColor scaleNO
Q12Other TypeColor scaleNO
Q12Other TypeColor scaleNO
Q12Other TypeColor scaleNO
Q12Other TypeColor scaleNO
Q12Other TypeColor scaleNO
Q12Other TypeColor scaleNO
Q12Other TypeColor scaleNO
Q12Cell Value=1textNO
I37Other TypeColor scaleNO
I37Other TypeColor scaleNO
I37Other TypeColor scaleNO
I37Other TypeColor scaleNO
I37Other TypeColor scaleNO
I37Other TypeColor scaleNO
I37Other TypeColor scaleNO
I37Other TypeColor scaleNO
I37Other TypeColor scaleNO
I37Cell Value=1textNO
K37Other TypeColor scaleNO
K37Other TypeColor scaleNO
K37Other TypeColor scaleNO
K37Other TypeColor scaleNO
K37Other TypeColor scaleNO
K37Other TypeColor scaleNO
K37Other TypeColor scaleNO
K37Other TypeColor scaleNO
K37Other TypeColor scaleNO
K37Cell Value=1textNO
M37Other TypeColor scaleNO
M37Other TypeColor scaleNO
M37Other TypeColor scaleNO
M37Other TypeColor scaleNO
M37Other TypeColor scaleNO
M37Other TypeColor scaleNO
M37Other TypeColor scaleNO
M37Other TypeColor scaleNO
M37Other TypeColor scaleNO
M37Cell Value=1textNO
O37Other TypeColor scaleNO
O37Other TypeColor scaleNO
O37Other TypeColor scaleNO
O37Other TypeColor scaleNO
O37Other TypeColor scaleNO
O37Other TypeColor scaleNO
O37Other TypeColor scaleNO
O37Other TypeColor scaleNO
O37Other TypeColor scaleNO
O37Cell Value=1textNO
Q37Other TypeColor scaleNO
Q37Other TypeColor scaleNO
Q37Other TypeColor scaleNO
Q37Other TypeColor scaleNO
Q37Other TypeColor scaleNO
Q37Other TypeColor scaleNO
Q37Other TypeColor scaleNO
Q37Other TypeColor scaleNO
Q37Other TypeColor scaleNO
Q37Cell Value=1textNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Cell Value=1textNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Cell Value=1textNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Cell Value=1textNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Cell Value=1textNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Cell Value=1textNO
Click to expand...
 
Upvote 0
Yes thank you for all your help. Ive been doing some formatting and trying to figure out how to add some percentages and overall completion percentage to the project rows so I may ask you something else tomorrow if you dont mind. As mentioned thank you for all your help I dont know what I would do without this forum.
 
Upvote 0
Morning,
I probably should have mentioned this when you was helping me with the other but at the time I wasnt sure how to explain but I think I may can now.

As you can see below on 2024 Sch sheet which was sheet 2 in our previous discussions there is percentages and dates added in columns I-Q with the average of that rows percentage in column F (I only have data in the rows under the 1st Quater but this will apply to all quaters with a project ID) All of the cells with percentages have conditional formatting. I am needing that over all percentage to show up in the 2024 BL and CL sheet which was previously sheet 1 in column F starting at row 17. I suppose it could be some code looking for the project ID and then appling that data but Ill leave that up to you.

I have noticed in testing that it appears the data in all colums except A,B,C,D & G on the 2024 Sch sheet doesnt seem to stay tied to its row when data is changed or new projects are added on the 2024 BL and CL sheet.

Franklin Job Review with Code.xlsm
ABCDEFGHIJKLMNO
1FMI's Current Backlong of Projects
2ProjectShip DateCustomerJob Type Status% CompNotes CONTRACT PRICEInvoiced %AMOUNT BILLED TO DATEMargin ESTIMATED GROSS PROFITCOSTS TO DATEFMI Cost GG Cost
3Ret- Jobs
4F2048-RET2Allied Lock
5F2559AL-RET2
6F2612-RET5
7F2646-RET3
8F3105-RET4
9F3208-RET4
10F3348-RET5
11F3544-RET2
12F3616A-RET1
13
14
15F- Jobs
16ProjectShip DateCustomerJob Type Status% CompNotes CONTRACT PRICEInvoiced %AMOUNT BILLED TO DATEMargin ESTIMATED GROSS PROFITCOSTS TO DATEFMI Cost GG Cost
17F3685Dec-2023BigBeeF3685-PTW LineShipped / Not Closed Complete95%$0.00
18F3668Dec-2023AceroF3668-Material Handling Line Shipped / Not Closed Load containers next week50%$0.00
19F3669Apr-2024ACEROF3669-20" Flange LinesFab/ Machine50%$0.00
20F3670Feb-2024ACEROF3670-PTW LineIn Machine Shop50%$0.00
21F3697Jan-2024New MilleniumF3697-4330 Angle ShearIn Machine Shopbegin35%$0.00
22F3699May-2024F3699- PTW 72FabReleased with F367035%$0.00
23F3700May-2024Standard StructuresF3700-Plasma System Fab35%$0.00
24F3701May-2024Standard StructuresF3701-Plate/Flange FabReleased with F369035%$0.00
25F3703Jan-2024CNH IndustriesF3703-Angle LineIn Machine Shop60%$0.00
26F3704Apr-2024Ideal SteelF3704-PTW Fab/ Machine35%$0.00
27F3705Feb-2024Chief BuildingsF3705- Plate/Flange LineFab/ Machine35%$0.00
28F3707Mar-2024ASTA DoorF3707-Angle Line with CoopersProgramming 35%$0.00
29F3710Nov-2024Lucas MetalsF3710-PTW 72Engineering35%$0.00
30F3711Nov-2024Lucas MetalsF3711-Plasma SystemEngineering35%$0.00
31F3712Nov-2024Lucas MetalsF3712-Plate/Flange Line Engineering35%$0.00
32F3713Dec-2024Valley JoistF3713-Valley Joist Angle LineEngineering35%$0.00
33F3714Dec-2024Vulcraft - TexasF3714-Long Span Angle LineEngineering35%$0.00
34F3715Jan-2025Central States F3715-Plasma LineEngineering35%$0.00
35F3716Jan-2025Central States F3716-PTW 72Engineering35%$0.00
36F3717Jan-2025Central States F3717-Plate/Flange Line Engineering35%$0.00
37F3718Aug-2024SiemansF3718-BusBarProgramming 20%$0.00
38F3719Aug-2024SiemansF3719-BusBarEngineering20%$0.00
39F3720Aug-2024SiemansF3720-BusBarEngineering20%$0.00
40F3721Aug-2024SiemansF3721-BusBarEngineering20%$0.00
41F3722Oct-2024SiemansF3722-BusBarEngineering20%$0.00
42F3723Oct-2024SiemansF3723-BusBarEngineering20%$0.00
43F3724Oct-2024SiemansF3724-BusBarEngineering20%$0.00
44F3725Mar-2024Bham Rail F3725 - Rail PressEngineering35%$0.00
45F3727Mar-2025ACI Buildings ExpansionF3727-72x240 slat table Engineering35%$0.00
46F3728Mar-2025ACI Buildings ExpansionF3728-Plate/Flange LineEngineering35%$0.00
47F3729Mar-2025ACI Buildings ExpansionF3729- PTW 72Engineering35%$0.00
48F3730Jan-2024SBS BuildingsF3730- Web seamersEngineering35%$0.00
49F3731Dec-2024AIG ControlsF3731- Shears / Nothers / PunchesEngineering$0.00
50F3777Jan-2024TestTestTest71%
51
52
53
2024 BL and CL
Cell Formulas
RangeFormula
J17:J49J17=H17*I17
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G16:G99Expression=$G16="Notes"textNO
A16:A99Expression=$A16="Project"+$A$16:$A$99textNO


Franklin Job Review with Code.xlsm
ABCDEFGHIJKLMNOPQ
1FMI's Current Backlong of Major Projects
2ProjectShip DateCustomerJob Type Status% CompNotes EngineeringPurchasing Machine ShopFab ShopAssembly
31st Quarter Jan-Mar 2024
4F3670Feb-2024ACEROF3670-PTW Line96%12/23/2023100%12/23/2023100%12/23/202380%12/23/2023100%12/23/2023100%
5F3697Jan-2024New MilleniumF3697-4330 Angle Shear100%begin12/23/2023100%12/23/2023100%12/23/2023100%12/23/2023100%12/23/2023100%
6F3703Jan-2024CNH IndustriesF3703-Angle Line54%12/23/2023100%12/23/2023100%2/1/202430%12/23/202330%12/23/202310%
7F3705Feb-2024Chief BuildingsF3705- Plate/Flange Line51%12/23/2023100%12/23/2023100%12/23/202330%12/23/202325%12/23/20230%
8F3707Mar-2024ASTA DoorF3707-Angle Line with Coopers45%12/23/2023100%12/23/2023100%12/23/20230%12/23/202325%12/23/20230%
9F3725Mar-2024Bham Rail F3725 - Rail Press60%12/23/2023100%1/31/2024100%12/23/2023100%1/31/20240%12/23/20230%
10F3730Jan-2024SBS BuildingsF3730- Web seamers70%12/23/2023100%12/23/2023100%12/23/202350%12/23/202350%12/23/202350%
11
122nd Quarter April-June 2024
13F3669Apr-2024ACEROF3669-20" Flange Lines
14F3699May-2024F3699- PTW 72Released with F3670
15F3700May-2024Standard StructuresF3700-Plasma System
16F3701May-2024Standard StructuresF3701-Plate/Flange Released with F3690
17F3704Apr-2024Ideal SteelF3704-PTW
18
193rd Quarter July-Sep 2024
20F3718Aug-2024SiemansF3718-BusBar
21F3719Aug-2024SiemansF3719-BusBar
22F3720Aug-2024SiemansF3720-BusBar
23F3721Aug-2024SiemansF3721-BusBar
24
254th Quarter Oct-Dec 2024
26F3710Nov-2024Lucas MetalsF3710-PTW 72
27F3711Nov-2024Lucas MetalsF3711-Plasma System
28F3712Nov-2024Lucas MetalsF3712-Plate/Flange Line
29F3713Dec-2024Valley JoistF3713-Valley Joist Angle Line
30F3714Dec-2024Vulcraft - TexasF3714-Long Span Angle Line
31F3722Oct-2024SiemansF3722-BusBar
32F3723Oct-2024SiemansF3723-BusBar
33F3724Oct-2024SiemansF3724-BusBar
34F3731Dec-2024AIG ControlsF3731- Shears / Nothers / Punches
35
365th Quarter 2025
37F3715Jan-2025Central States F3715-Plasma Line
38F3716Jan-2025Central States F3716-PTW 72
39F3717Jan-2025Central States F3717-Plate/Flange Line
40F3727Mar-2025ACI Buildings ExpansionF3727-72x240 slat table
41F3728Mar-2025ACI Buildings ExpansionF3728-Plate/Flange Line
42F3729Mar-2025ACI Buildings ExpansionF3729- PTW 72
43
2024 Sch
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G4Expression=$G4="Notes"textNO
A4Expression=$A4="Project"+$A$16:$A$99textNO
F4:F11Other TypeColor scaleNO
I4:I11,K4:K11,M4:M11,O4:O11,Q4:Q11Other TypeColor scaleNO
E4:E6,G5:G6Expression=$A4<>""textNO
A12:Q100,A7:G10,A11:H11,P11,N11,L11,J11,F4:F6Expression=$A4<>""textNO
I35Other TypeColor scaleNO
I35Other TypeColor scaleNO
I35Other TypeColor scaleNO
I35Other TypeColor scaleNO
I35Other TypeColor scaleNO
I35Other TypeColor scaleNO
I35Other TypeColor scaleNO
I35Other TypeColor scaleNO
I35Other TypeColor scaleNO
I35Cell Value=1textNO
K35Other TypeColor scaleNO
K35Other TypeColor scaleNO
K35Other TypeColor scaleNO
K35Other TypeColor scaleNO
K35Other TypeColor scaleNO
K35Other TypeColor scaleNO
K35Other TypeColor scaleNO
K35Other TypeColor scaleNO
K35Other TypeColor scaleNO
K35Cell Value=1textNO
M35Other TypeColor scaleNO
M35Other TypeColor scaleNO
M35Other TypeColor scaleNO
M35Other TypeColor scaleNO
M35Other TypeColor scaleNO
M35Other TypeColor scaleNO
M35Other TypeColor scaleNO
M35Other TypeColor scaleNO
M35Other TypeColor scaleNO
M35Cell Value=1textNO
O35Other TypeColor scaleNO
O35Other TypeColor scaleNO
O35Other TypeColor scaleNO
O35Other TypeColor scaleNO
O35Other TypeColor scaleNO
O35Other TypeColor scaleNO
O35Other TypeColor scaleNO
O35Other TypeColor scaleNO
O35Other TypeColor scaleNO
O35Cell Value=1textNO
Q35Other TypeColor scaleNO
Q35Other TypeColor scaleNO
Q35Other TypeColor scaleNO
Q35Other TypeColor scaleNO
Q35Other TypeColor scaleNO
Q35Other TypeColor scaleNO
Q35Other TypeColor scaleNO
Q35Other TypeColor scaleNO
Q35Other TypeColor scaleNO
Q35Cell Value=1textNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Other TypeColor scaleNO
I43Cell Value=1textNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Other TypeColor scaleNO
K43Cell Value=1textNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Other TypeColor scaleNO
M43Cell Value=1textNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Other TypeColor scaleNO
O43Cell Value=1textNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Other TypeColor scaleNO
Q43Cell Value=1textNO
 
Upvote 0
Place this macro in the worksheet code module for the "2024 Sch" sheet. Each time you de-activate this sheet, the "2024 BL and CL" sheet will be updated with the % Comp.
VBA Code: 
Private Sub Worksheet_Deactivate()
    Application.ScreenUpdating = False
    Dim rng As Range, lRow As Long, fnd As Range
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Range("F4:F" & lRow)
        If rng <> "" Then
            Set fnd = Sheets("2024 BL and CL").Range("A:A").Find(rng.Offset(, -5).Value)
            If Not fnd Is Nothing Then
                fnd.Offset(, 5) = rng
            End If
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub
bamaisgreat said:
it appears the data in all columns except A,B,C,D & G on the 2024 Sch sheet doesn't seem to stay tied to its row
Click to expand...
I couldn't replicate this issue. Could you explain in detail what is happening?
 
Upvote 0
Mumps, sorry for the delayed reply. We have been snowed in down here where I live.

For some reason percentage is not showing up in column F on the 2024 sch sheet. For example: 2024 Sch sheet row 25 I added some percentages in columns I,K,M,O & Q and the overall average is not showing in column F. I have a few at the top of the page showing but I manually put them in. Please take a look if you have time. Thanks Jamey

Franklin Job Review with Code.xlsm
ABCDEFGHIJKLMNOPQ
1FMI's Current Backlong of Major Projects
2ProjectShip DateCustomerJob Type Status% CompNotes EngineeringPurchasing Machine ShopFab ShopAssembly
31st Quarter Jan-Mar 2024
4F3670Feb-2024ACEROF3670-PTW Line96.00%12/23/2023100%12/23/2023100%12/23/202380%12/23/2023100%12/23/2023100%
5F3697Jan-2024New MilleniumF3697-4330 Angle Shear100.00%begin12/23/2023100%12/23/2023100%12/23/2023100%12/23/2023100%12/23/2023100%
6F3703Jan-2024CNH IndustriesF3703-Angle Line54.00%12/23/2023100%12/23/2023100%2/1/202430%12/23/202330%12/23/202310%
7F3705Feb-2024Chief BuildingsF3705- Plate/Flange Line51.00%12/23/2023100%12/23/2023100%12/23/202330%12/23/202325%12/23/20230%
8F3707Mar-2024ASTA DoorF3707-Angle Line with Coopers45.00%12/23/2023100%12/23/2023100%12/23/20230%12/23/202325%12/23/20230%
9F3725Mar-2024Bham Rail F3725 - Rail Press60.00%12/23/2023100%1/31/2024100%12/23/2023100%1/31/20240%12/23/20230%
10F3730Jan-2024SBS BuildingsF3730- Web seamers70.00%12/23/2023100%12/23/2023100%12/23/202350%12/23/202350%12/23/202350%
11
122nd Quarter April-June 2024
13F3669Apr-2024ACEROF3669-20" Flange Lines
14F3699May-2024F3699- PTW 72Released with F3670
15F3700May-2024Standard StructuresF3700-Plasma System
16F3701May-2024Standard StructuresF3701-Plate/Flange Released with F3690
17F3704Apr-2024Ideal SteelF3704-PTW
18
193rd Quarter July-Sep 2024
20F3718Aug-2024SiemansF3718-BusBar
21F3719Aug-2024SiemansF3719-BusBar
22F3721Aug-2024SiemansF3721-BusBar
23
244th Quarter Oct-Dec 2024
25F3710Dec-2024Lucas MetalsF3710-PTW 7210%10%10%10%10%
26F3711Nov-2024Lucas MetalsF3711-Plasma System
27F3712Nov-2024Lucas MetalsF3712-Plate/Flange Line
28F3713Dec-2024Valley JoistF3713-Valley Joist Angle Line
29F3714Dec-2024Vulcraft - TexasF3714-Long Span Angle Line
30F3722Oct-2024SiemansF3722-BusBar
31F3723Oct-2024SiemansF3723-BusBar
32F3724Oct-2024SiemansF3724-BusBar
33F3731Dec-2024AIG ControlsF3731- Shears / Nothers / Punches
34
355th Quarter 2025
36F3715Jan-2025Central States F3715-Plasma Line
37F3716Jan-2025Central States F3716-PTW 72
38F3717Jan-2025Central States F3717-Plate/Flange Line
39F3727Mar-2025ACI Buildings ExpansionF3727-72x240 slat table
40F3728Mar-2025ACI Buildings ExpansionF3728-Plate/Flange Line
41F3729Mar-2025ACI Buildings ExpansionF3729- PTW 72
42
2024 Sch
Cell Formulas
RangeFormula
F4:F10F4=AVERAGE(I4,K4,M4,O4,Q4)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G7Expression=$G7="Notes"textNO
F3:F1048576,F1Other TypeColor scaleNO
I:I,K:K,M:M,O:O,Q:QOther TypeColor scaleNO
A11:H11,P11,N11,L11,J11,A8:G10,F8:F11,A12:Q24,E7:F7,A26:Q99,E25:F25,H25:Q25Expression=$A7<>""textNO
I34Other TypeColor scaleNO
I34Other TypeColor scaleNO
I34Other TypeColor scaleNO
I34Other TypeColor scaleNO
I34Other TypeColor scaleNO
I34Other TypeColor scaleNO
I34Other TypeColor scaleNO
I34Other TypeColor scaleNO
I34Other TypeColor scaleNO
I34Cell Value=1textNO
K34Other TypeColor scaleNO
K34Other TypeColor scaleNO
K34Other TypeColor scaleNO
K34Other TypeColor scaleNO
K34Other TypeColor scaleNO
K34Other TypeColor scaleNO
K34Other TypeColor scaleNO
K34Other TypeColor scaleNO
K34Other TypeColor scaleNO
K34Cell Value=1textNO
M34Other TypeColor scaleNO
M34Other TypeColor scaleNO
M34Other TypeColor scaleNO
M34Other TypeColor scaleNO
M34Other TypeColor scaleNO
M34Other TypeColor scaleNO
M34Other TypeColor scaleNO
M34Other TypeColor scaleNO
M34Other TypeColor scaleNO
M34Cell Value=1textNO
O34Other TypeColor scaleNO
O34Other TypeColor scaleNO
O34Other TypeColor scaleNO
O34Other TypeColor scaleNO
O34Other TypeColor scaleNO
O34Other TypeColor scaleNO
O34Other TypeColor scaleNO
O34Other TypeColor scaleNO
O34Other TypeColor scaleNO
O34Cell Value=1textNO
Q34Other TypeColor scaleNO
Q34Other TypeColor scaleNO
Q34Other TypeColor scaleNO
Q34Other TypeColor scaleNO
Q34Other TypeColor scaleNO
Q34Other TypeColor scaleNO
Q34Other TypeColor scaleNO
Q34Other TypeColor scaleNO
Q34Other TypeColor scaleNO
Q34Cell Value=1textNO
I42Other TypeColor scaleNO
I42Other TypeColor scaleNO
I42Other TypeColor scaleNO
I42Other TypeColor scaleNO
I42Other TypeColor scaleNO
I42Other TypeColor scaleNO
I42Other TypeColor scaleNO
I42Other TypeColor scaleNO
I42Other TypeColor scaleNO
I42Cell Value=1textNO
K42Other TypeColor scaleNO
K42Other TypeColor scaleNO
K42Other TypeColor scaleNO
K42Other TypeColor scaleNO
K42Other TypeColor scaleNO
K42Other TypeColor scaleNO
K42Other TypeColor scaleNO
K42Other TypeColor scaleNO
K42Other TypeColor scaleNO
K42Cell Value=1textNO
M42Other TypeColor scaleNO
M42Other TypeColor scaleNO
M42Other TypeColor scaleNO
M42Other TypeColor scaleNO
M42Other TypeColor scaleNO
M42Other TypeColor scaleNO
M42Other TypeColor scaleNO
M42Other TypeColor scaleNO
M42Other TypeColor scaleNO
M42Cell Value=1textNO
O42Other TypeColor scaleNO
O42Other TypeColor scaleNO
O42Other TypeColor scaleNO
O42Other TypeColor scaleNO
O42Other TypeColor scaleNO
O42Other TypeColor scaleNO
O42Other TypeColor scaleNO
O42Other TypeColor scaleNO
O42Other TypeColor scaleNO
O42Cell Value=1textNO
Q42Other TypeColor scaleNO
Q42Other TypeColor scaleNO
Q42Other TypeColor scaleNO
Q42Other TypeColor scaleNO
Q42Other TypeColor scaleNO
Q42Other TypeColor scaleNO
Q42Other TypeColor scaleNO
Q42Other TypeColor scaleNO
Q42Other TypeColor scaleNO
Q42Cell Value=1textNO
A7Expression=$A7="Project"+$A$16:$A$98textNO



Franklin Job Review with Code.xlsm
ABCDEFGHIJKLMNO
1FMI's Current Backlong of Projects
2ProjectShip DateCustomerJob Type Status% CompNotes CONTRACT PRICEInvoiced %AMOUNT BILLED TO DATEMargin ESTIMATED GROSS PROFITCOSTS TO DATEFMI Cost GG Cost
3Ret- Jobs
4F2048-RET2Allied Lock
5F2559AL-RET2
6F2612-RET5
7F2646-RET3
8F3105-RET4
9F3208-RET4
10F3348-RET5
11F3544-RET2
12F3616A-RET1
13
14
15F- Jobs
16ProjectShip DateCustomerJob Type Status% CompNotes CONTRACT PRICEInvoiced %AMOUNT BILLED TO DATEMargin ESTIMATED GROSS PROFITCOSTS TO DATEFMI Cost GG Cost
17F3685Dec-2023BigBeeF3685-PTW LineShipped / Not Closed Complete95%$0.00
18F3668Dec-2023AceroF3668-Material Handling Line Shipped / Not Closed Load containers next week50%$0.00
19F3669Apr-2024ACEROF3669-20" Flange LinesFab/ Machine50%$0.00
20F3670Feb-2024ACEROF3670-PTW LineIn Machine Shop96%50%$0.00
21F3697Jan-2024New MilleniumF3697-4330 Angle ShearIn Machine Shop100%begin35%$0.00
22F3699May-2024F3699- PTW 72Fab / MachineReleased with F367035%$0.00
23F3700May-2024Standard StructuresF3700-Plasma System Fab35%$0.00
24F3701May-2024Standard StructuresF3701-Plate/Flange Fab26%Released with F369035%$0.00
25F3703Jan-2024CNH IndustriesF3703-Angle LineIn Machine Shop54%60%$0.00
26F3704Apr-2024Ideal SteelF3704-PTW Fab/ Machine35%$0.00
27F3705Feb-2024Chief BuildingsF3705- Plate/Flange LineFab/ Machine51%35%$0.00
28F3707Mar-2024ASTA DoorF3707-Angle Line with CoopersProgramming 45%35%$0.00
29F3710Dec-2024Lucas MetalsF3710-PTW 72Engineering35%$0.00
30F3711Nov-2024Lucas MetalsF3711-Plasma SystemEngineering35%$0.00
31F3712Nov-2024Lucas MetalsF3712-Plate/Flange Line Engineering35%$0.00
32F3713Dec-2024Valley JoistF3713-Valley Joist Angle LineEngineering35%$0.00
33F3714Dec-2024Vulcraft - TexasF3714-Long Span Angle LineEngineering35%$0.00
34F3715Jan-2025Central States F3715-Plasma LineEngineering35%$0.00
35F3716Jan-2025Central States F3716-PTW 72Engineering35%$0.00
36F3717Jan-2025Central States F3717-Plate/Flange Line Engineering35%$0.00
37F3718Aug-2024SiemansF3718-BusBarProgramming 20%$0.00
38F3719Aug-2024SiemansF3719-BusBarEngineering20%$0.00
39F3720Aug-2024SiemansF3720-BusBarEngineering5000%20%$0.00
40F3721Aug-2024SiemansF3721-BusBarEngineering20%$0.00
41F3722Oct-2024SiemansF3722-BusBarEngineering20%$0.00
42F3723Oct-2024SiemansF3723-BusBarEngineering20%$0.00
43F3724Oct-2024SiemansF3724-BusBarEngineering20%$0.00
44F3725Mar-2024Bham Rail F3725 - Rail PressEngineering60%35%$0.00
45F3727Mar-2025ACI Buildings ExpansionF3727-72x240 slat table Engineering35%$0.00
46F3728Mar-2025ACI Buildings ExpansionF3728-Plate/Flange LineEngineering35%$0.00
47F3729Mar-2025ACI Buildings ExpansionF3729- PTW 72Engineering35%$0.00
48F3730Jan-2024SBS BuildingsF3730- Web seamersEngineering70%35%$0.00
49F3731Dec-2024AIG ControlsF3731- Shears / Nothers / PunchesEngineering$0.00
50
51
52
53
54
55
2024 BL and CL
Cell Formulas
RangeFormula
J17:J49J17=H17*I17
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F17:F98Other TypeColor scaleNO
Cells with Data Validation
CellAllowCriteria
E17:E55ListShipped / Not Closed, Fab / Machine, In Machine Shop, Engineering, Programming
 
Upvote 0
Your formulas are missing in column F.
 
Upvote 0
mumps said:
Your formulas are missing in column F.
Click to expand...
Ok, that reminds me of the other question I meant to ask. Im not sure how to apply a formula to complete column like that has headers. When I add the formula I get a mismatch error in vba.

Is there a way to add the formula to column in vba? When I add a new project on the 2024 BL and CL sheet and it copies it to the 2024 sch sheet I have to manually enter the formula in.



Franklin Job Review with Code.xlsm
ABCDEFGHIJKLMNO
1FMI's Current Backlong of Projects
2ProjectShip DateCustomerJob Type Status% CompNotes CONTRACT PRICEInvoiced %AMOUNT BILLED TO DATEMargin ESTIMATED GROSS PROFITCOSTS TO DATEFMI Cost GG Cost
3Ret- Jobs
4F2048-RET2Allied Lock
5F2559AL-RET2
6F2612-RET5
7F2646-RET3
8F3105-RET4
9F3208-RET4
10F3348-RET5
11F3544-RET2
12F3616A-RET1
13
14
15F- Jobs
16ProjectShip DateCustomerJob Type Status% CompNotes CONTRACT PRICEInvoiced %AMOUNT BILLED TO DATEMargin ESTIMATED GROSS PROFITCOSTS TO DATEFMI Cost GG Cost
17F3685Dec-2023BigBeeF3685-PTW LineShipped / Not Closed Complete95%$0.00
18F3668Dec-2023AceroF3668-Material Handling Line Shipped / Not Closed Load containers next week50%$0.00
19F3669Apr-2024ACEROF3669-20" Flange LinesFab/ Machine50%$0.00
20F3670Feb-2024ACEROF3670-PTW LineIn Machine Shop96%50%$0.00
21F3697Jan-2024New MilleniumF3697-4330 Angle ShearIn Machine Shop100%begin35%$0.00
22F3699May-2024F3699- PTW 72Fab / MachineReleased with F367035%$0.00
23F3700May-2024Standard StructuresF3700-Plasma System Fab35%$0.00
24F3701May-2024Standard StructuresF3701-Plate/Flange Fab26%Released with F369035%$0.00
25F3703Jan-2024CNH IndustriesF3703-Angle LineIn Machine Shop54%60%$0.00
26F3704Apr-2024Ideal SteelF3704-PTW Fab/ Machine35%$0.00
27F3705Feb-2024Chief BuildingsF3705- Plate/Flange LineFab/ Machine51%35%$0.00
28F3707Mar-2024ASTA DoorF3707-Angle Line with CoopersProgramming 45%35%$0.00
29F3710Dec-2024Lucas MetalsF3710-PTW 72Engineering35%$0.00
30F3711Nov-2024Lucas MetalsF3711-Plasma SystemEngineering35%$0.00
31F3712Nov-2024Lucas MetalsF3712-Plate/Flange Line Engineering35%$0.00
32F3713Dec-2024Valley JoistF3713-Valley Joist Angle LineEngineering35%$0.00
33F3714Dec-2024Vulcraft - TexasF3714-Long Span Angle LineEngineering35%$0.00
34F3715Jan-2025Central States F3715-Plasma LineEngineering35%$0.00
35F3716Jan-2025Central States F3716-PTW 72Engineering35%$0.00
36F3717Jan-2025Central States F3717-Plate/Flange Line Engineering35%$0.00
37F3718Aug-2024SiemansF3718-BusBarProgramming 20%$0.00
38F3719Aug-2024SiemansF3719-BusBarEngineering20%$0.00
39F3721Aug-2024SiemansF3721-BusBarEngineering20%$0.00
40F3722Oct-2024SiemansF3722-BusBarEngineering20%$0.00
41F3723Oct-2024SiemansF3723-BusBarEngineering20%$0.00
42F3724Oct-2024SiemansF3724-BusBarEngineering20%$0.00
43F3725Mar-2024Bham Rail F3725 - Rail PressEngineering60%35%$0.00
44F3727Mar-2025ACI Buildings ExpansionF3727-72x240 slat table Engineering35%$0.00
45F3728Mar-2025ACI Buildings ExpansionF3728-Plate/Flange LineEngineering35%$0.00
46F3729Mar-2025ACI Buildings ExpansionF3729- PTW 72Engineering35%$0.00
47F3730Jan-2024SBS BuildingsF3730- Web seamersEngineering70%35%$0.00
48F3731Dec-2024AIG ControlsF3731- Shears / Nothers / PunchesEngineering$0.00
49
50
2024 BL and CL
Cell Formulas
RangeFormula
J17:J48J17=H17*I17
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F17:F97Other TypeColor scaleNO
Cells with Data Validation
CellAllowCriteria
E17:E50ListShipped / Not Closed, Fab / Machine, In Machine Shop, Engineering, Programming



Franklin Job Review with Code.xlsm
ABCDEFGHIJKLMNOPQ
1FMI's Current Backlong of Major Projects
2ProjectShip DateCustomerJob Type Status% CompNotes EngineeringPurchasing Machine ShopFab ShopAssembly
31st Quarter Jan-Mar 2024
4F3670Feb-2024ACEROF3670-PTW Line96.00%12/23/2023100%12/23/2023100%12/23/202380%12/23/2023100%12/23/2023100%
5F3697Jan-2024New MilleniumF3697-4330 Angle Shear100.00%begin12/23/2023100%12/23/2023100%12/23/2023100%12/23/2023100%12/23/2023100%
6F3703Jan-2024CNH IndustriesF3703-Angle Line54.00%12/23/2023100%12/23/2023100%2/1/202430%12/23/202330%12/23/202310%
7F3705Feb-2024Chief BuildingsF3705- Plate/Flange Line51.00%12/23/2023100%12/23/2023100%12/23/202330%12/23/202325%12/23/20230%
8F3707Mar-2024ASTA DoorF3707-Angle Line with Coopers45.00%12/23/2023100%12/23/2023100%12/23/20230%12/23/202325%12/23/20230%
9F3725Mar-2024Bham Rail F3725 - Rail Press60.00%12/23/2023100%1/31/2024100%12/23/2023100%1/31/20240%12/23/20230%
10F3730Jan-2024SBS BuildingsF3730- Web seamers70.00%12/23/2023100%12/23/2023100%12/23/202350%12/23/202350%12/23/202350%
11#DIV/0!
12#DIV/0!2nd Quarter April-June 2024
13F3669Apr-2024ACEROF3669-20" Flange Lines#DIV/0!
14F3699May-2024F3699- PTW 72#DIV/0!Released with F3670
15F3700May-2024Standard StructuresF3700-Plasma System #DIV/0!
16F3701May-2024Standard StructuresF3701-Plate/Flange #DIV/0!Released with F3690
17F3704Apr-2024Ideal SteelF3704-PTW #DIV/0!
18#DIV/0!
19#DIV/0!3rd Quarter July-Sep 2024
20F3718Aug-2024SiemansF3718-BusBar#DIV/0!
21F3719Aug-2024SiemansF3719-BusBar#DIV/0!
22F3721Aug-2024SiemansF3721-BusBar#DIV/0!
23#DIV/0!
24#DIV/0!4th Quarter Oct-Dec 2024
25F3710Dec-2024Lucas MetalsF3710-PTW 72#DIV/0!
26F3711Nov-2024Lucas MetalsF3711-Plasma System#DIV/0!
27F3712Nov-2024Lucas MetalsF3712-Plate/Flange Line #DIV/0!
28F3713Dec-2024Valley JoistF3713-Valley Joist Angle Line#DIV/0!
29F3714Dec-2024Vulcraft - TexasF3714-Long Span Angle Line#DIV/0!
30F3722Oct-2024SiemansF3722-BusBar#DIV/0!
31F3723Oct-2024SiemansF3723-BusBar#DIV/0!
32F3724Oct-2024SiemansF3724-BusBar#DIV/0!
33F3731Dec-2024AIG ControlsF3731- Shears / Nothers / Punches#DIV/0!
34#DIV/0!
35#DIV/0!5th Quarter 2025
36F3715Jan-2025Central States F3715-Plasma Line#DIV/0!
37F3716Jan-2025Central States F3716-PTW 72#DIV/0!
38F3717Jan-2025Central States F3717-Plate/Flange Line #DIV/0!
39F3727Mar-2025ACI Buildings ExpansionF3727-72x240 slat table #DIV/0!
40F3728Mar-2025ACI Buildings ExpansionF3728-Plate/Flange Line#DIV/0!
41F3729Mar-2025ACI Buildings ExpansionF3729- PTW 72#DIV/0!
42#DIV/0!
2024 Sch
Cell Formulas
RangeFormula
F4:F42F4=AVERAGE(I4,K4,M4,O4,Q4)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G7Expression=$G7="Notes"textNO
F3:F1048576,F1Other TypeColor scaleNO
I:I,K:K,M:M,O:O,Q:QOther TypeColor scaleNO
A11:H11,P11,N11,L11,J11,A8:G10,F8:F11,A12:Q24,E7:F7,A26:Q32,E25:F25,H25:Q25,A34:Q99,E33:F33,H33:Q33Expression=$A7<>""textNO
I34Other TypeColor scaleNO
I34Other TypeColor scaleNO
I34Other TypeColor scaleNO
I34Other TypeColor scaleNO
I34Other TypeColor scaleNO
I34Other TypeColor scaleNO
I34Other TypeColor scaleNO
I34Other TypeColor scaleNO
I34Other TypeColor scaleNO
I34Cell Value=1textNO
K34Other TypeColor scaleNO
K34Other TypeColor scaleNO
K34Other TypeColor scaleNO
K34Other TypeColor scaleNO
K34Other TypeColor scaleNO
K34Other TypeColor scaleNO
K34Other TypeColor scaleNO
K34Other TypeColor scaleNO
K34Other TypeColor scaleNO
K34Cell Value=1textNO
M34Other TypeColor scaleNO
M34Other TypeColor scaleNO
M34Other TypeColor scaleNO
M34Other TypeColor scaleNO
M34Other TypeColor scaleNO
M34Other TypeColor scaleNO
M34Other TypeColor scaleNO
M34Other TypeColor scaleNO
M34Other TypeColor scaleNO
M34Cell Value=1textNO
O34Other TypeColor scaleNO
O34Other TypeColor scaleNO
O34Other TypeColor scaleNO
O34Other TypeColor scaleNO
O34Other TypeColor scaleNO
O34Other TypeColor scaleNO
O34Other TypeColor scaleNO
O34Other TypeColor scaleNO
O34Other TypeColor scaleNO
O34Cell Value=1textNO
Q34Other TypeColor scaleNO
Q34Other TypeColor scaleNO
Q34Other TypeColor scaleNO
Q34Other TypeColor scaleNO
Q34Other TypeColor scaleNO
Q34Other TypeColor scaleNO
Q34Other TypeColor scaleNO
Q34Other TypeColor scaleNO
Q34Other TypeColor scaleNO
Q34Cell Value=1textNO
I42Other TypeColor scaleNO
I42Other TypeColor scaleNO
I42Other TypeColor scaleNO
I42Other TypeColor scaleNO
I42Other TypeColor scaleNO
I42Other TypeColor scaleNO
I42Other TypeColor scaleNO
I42Other TypeColor scaleNO
I42Other TypeColor scaleNO
I42Cell Value=1textNO
K42Other TypeColor scaleNO
K42Other TypeColor scaleNO
K42Other TypeColor scaleNO
K42Other TypeColor scaleNO
K42Other TypeColor scaleNO
K42Other TypeColor scaleNO
K42Other TypeColor scaleNO
K42Other TypeColor scaleNO
K42Other TypeColor scaleNO
K42Cell Value=1textNO
M42Other TypeColor scaleNO
M42Other TypeColor scaleNO
M42Other TypeColor scaleNO
M42Other TypeColor scaleNO
M42Other TypeColor scaleNO
M42Other TypeColor scaleNO
M42Other TypeColor scaleNO
M42Other TypeColor scaleNO
M42Other TypeColor scaleNO
M42Cell Value=1textNO
O42Other TypeColor scaleNO
O42Other TypeColor scaleNO
O42Other TypeColor scaleNO
O42Other TypeColor scaleNO
O42Other TypeColor scaleNO
O42Other TypeColor scaleNO
O42Other TypeColor scaleNO
O42Other TypeColor scaleNO
O42Other TypeColor scaleNO
O42Cell Value=1textNO
Q42Other TypeColor scaleNO
Q42Other TypeColor scaleNO
Q42Other TypeColor scaleNO
Q42Other TypeColor scaleNO
Q42Other TypeColor scaleNO
Q42Other TypeColor scaleNO
Q42Other TypeColor scaleNO
Q42Other TypeColor scaleNO
Q42Other TypeColor scaleNO
Q42Cell Value=1textNO
A7Expression=$A7="Project"+$A$16:$A$98textNO
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Copy a certain set of data from one sheet to another depending on the date typed in
2
Replies
10
Views
506
myall_blues
myall_blues
A
  • Solved
take amount from last row in listbox on form and put into next column
Replies
7
Views
144
Abdo
A
Drrellik
  • Solved
VBA to complete Index/Match with 2 criteria
2
Replies
10
Views
422
Alex Blakenburg
A
Dazzawm
  • Solved
Macro To Find Latest Date To Corresponding Data In Column B And Add To Sheet 2
Replies
7
Views
341
Fuji
F
M
  • Question Question
Macro or Power Automate to Identify and Move "Washes" from the "Master" Sheet to the "Washes" Sheet
Replies
0
Views
182
Madam_Xavier
M

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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