Hi,
Nice to join the forum, I am reasonably experienced in Excel but a complete beginner in VBA. I have just done a couple of basic training courses but still feel a little overwhelmed when trying
to start a new project.
I have a deliverables list that we use to track our drawing issues. It contains a variable number of rows and columns, with some columns prepopulated for the Planned issue dates, we then fill
in the dates when the deliverables are actually issued.
I have set the spreadsheet to use a HLOOKUP and a hidden table sheet to automatically populate the current Issue status of each deliverable in column C.
Look Up Sheet
In order to ignore the two Planned Date columns I have had to split the formulas (columns E & F) and then add a third (column D) to consolidate that information.
The formulas in columns D, E & F are these respectively, each one finds the last populated cell and gets the header name for that column.
Column D, E & F are normally hidden columns that contain these formula;
Col D: =LOOKUP(2,1/(E3:F3<>""),E3:F3)
Col E: =IF(COUNTA(I3:J3)=0,"",INDEX($I$1:$J$1,MATCH(LOOKUP(2,1/(I3:J3<>""),I3:J3),I3:J3,0)))
Col F: =IF(COUNTA(L3:AD3)=0,"",INDEX($L$1:$AD$1,MATCH(LOOKUP(2,1/(L3:AD3<>""),L3:AD3),L3:AD3,0)))
This works but does not strike me as a very elegant way of doing the job.
I would like to do this with VBA but honestly do not know where to start.
I have attached a copy of the sheet, if anybody could be so kind as to give me some pointers of where to start and how to go about this I would be most grateful.
Thank you very much.
Mick
Nice to join the forum, I am reasonably experienced in Excel but a complete beginner in VBA. I have just done a couple of basic training courses but still feel a little overwhelmed when trying
to start a new project.
I have a deliverables list that we use to track our drawing issues. It contains a variable number of rows and columns, with some columns prepopulated for the Planned issue dates, we then fill
in the dates when the deliverables are actually issued.
I have set the spreadsheet to use a HLOOKUP and a hidden table sheet to automatically populate the current Issue status of each deliverable in column C.
Look Up Sheet
In order to ignore the two Planned Date columns I have had to split the formulas (columns E & F) and then add a third (column D) to consolidate that information.
The formulas in columns D, E & F are these respectively, each one finds the last populated cell and gets the header name for that column.
Column D, E & F are normally hidden columns that contain these formula;
Col D: =LOOKUP(2,1/(E3:F3<>""),E3:F3)
Col E: =IF(COUNTA(I3:J3)=0,"",INDEX($I$1:$J$1,MATCH(LOOKUP(2,1/(I3:J3<>""),I3:J3),I3:J3,0)))
Col F: =IF(COUNTA(L3:AD3)=0,"",INDEX($L$1:$AD$1,MATCH(LOOKUP(2,1/(L3:AD3<>""),L3:AD3),L3:AD3,0)))
This works but does not strike me as a very elegant way of doing the job.
I would like to do this with VBA but honestly do not know where to start.
I have attached a copy of the sheet, if anybody could be so kind as to give me some pointers of where to start and how to go about this I would be most grateful.
Thank you very much.
Mick
| Drawing Issue Register v3.xlsx | ||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | |||
| 1 | TITLE | ISSUED | LAST ISSUED STATUS | BEFORE PLANNED AFC | AFTER PLANNED AFC | Planned IFR (00) Date | Issued IFR (00) | CPY comment (00) | Planned AFC (01) Date | Issued AFC (01) | CPY comment (01) | Reissued AFC (02) | CPY comment (02) | Reissued AFC (03) | CPY comment (03) | Reissued AFC (04) | CPY comment (04) | Reissued AFC (05) | CPY comment (05) | |||
| 2 | ||||||||||||||||||||||
| 3 | CPF - POWERGEN WBS-4100 - GTG FOUNDATIONS - KEY PLAN | 02 | Reissued AFC (02) | Issued IFR (00) | Reissued AFC (02) | 25/05/2022 | 11-May-22 | 01/12/2022 | 12-Dec-22 | 07-Feb-23 | ||||||||||||
| 4 | CPF - POWERGEN WBS-4100 - GTG FOUNDATION GENERAL ARRANGEMENT - PLAN | 01 | Issued AFC (01) | Issued IFR (00) | Issued AFC (01) | 25/05/2022 | 11-May-22 | 01/12/2022 | 12-Dec-22 | |||||||||||||
| 5 | CPF - POWERGEN WBS-4100 - GTG FOUNDATION GENERAL ARRANGEMENT - LONGITUDINAL & TRANSVERSE SECTIONS | 02 | Reissued AFC (02) | Issued IFR (00) | Reissued AFC (02) | 25/05/2022 | 11-May-22 | 01/12/2022 | 12-Dec-22 | 02-Feb-23 | ||||||||||||
| 6 | CPF - POWERGEN WBS-4100 - GTG FOUNDATION RC DETAILS - TOP REINFORCEMENT | 01 | Issued AFC (01) | Issued IFR (00) | Issued AFC (01) | 25/05/2022 | 19-May-22 | 01/12/2022 | 19-Dec-22 | |||||||||||||
| 7 | CPF - POWERGEN WBS-4100 - GTG FOUNDATION RC DETAILS - MIDDLE REINFORCEMENT | 01 | Issued AFC (01) | Issued IFR (00) | Issued AFC (01) | 25/05/2022 | 19-May-22 | 01/12/2022 | 19-Dec-22 | |||||||||||||
| 8 | CPF - POWERGEN WBS-4100 - GTG FOUNDATION RC DETAILS - BOTTOM REINFORCEMENT | 02 | Reissued AFC (02) | Issued IFR (00) | Reissued AFC (02) | 25/05/2022 | 19-May-22 | 01/12/2022 | 19-Dec-22 | 03-Feb-23 | ||||||||||||
| 9 | CPF - POWERGEN WBS-4100 - GTG FOUNDATION RC DETAILS - PLINTH REINFORCEMENT - SHEET 1 | 01 | Issued AFC (01) | Issued IFR (00) | Issued AFC (01) | 25/05/2022 | 19-Dec-22 | 01/12/2022 | 03-Feb-23 | |||||||||||||
| 10 | CPF - POWERGEN WBS-4100 - GTG FOUNDATION RC DETAILS - PLINTH REINFORCEMENT - SHEET 2 | 01 | Issued AFC (01) | Issued IFR (00) | Issued AFC (01) | 25/05/2022 | 19-Dec-22 | 01/12/2022 | 03-Feb-23 | |||||||||||||
| 11 | CPF - POWERGEN WBS-4100 - GTG FOUNDATION RC DETAILS - POCKET REINFORCEMENT | 01 | Issued AFC (01) | Issued IFR (00) | Issued AFC (01) | 25/05/2022 | 19-Dec-22 | 01/12/2022 | 03-Feb-23 | |||||||||||||
| 12 | CPF - POWERGEN WBS-4100 - GTG FOUNDATION - DUCTS - PLAN | 01 | Issued AFC (01) | Issued IFR (00) | Issued AFC (01) | 25/05/2022 | 12-Dec-22 | 01/12/2022 | 07-Feb-23 | |||||||||||||
| 13 | CPF - POWERGEN WBS-4100 - GTG FOUNDATION ELECTRICAL - DUCTS - ELEVATIONS | 01 | Issued AFC (01) | Issued IFR (00) | Issued AFC (01) | 25/05/2022 | 12-Dec-22 | 01/12/2022 | 07-Feb-23 | |||||||||||||
| 14 | CPF - POWERGEN WBS-4100 - EXHAUST STACK FOUNDATION - PLANS, SECTIONS AND RC DETAILS - SHEET 1 | 02 | Reissued AFC (02) | Issued IFR (00) | Reissued AFC (02) | 25/05/2022 | 19-Aug-22 | 01/12/2022 | 06-Dec-22 | 06-Apr-23 | ||||||||||||
| 15 | CPF - POWERGEN WBS-4100 - EXHAUST STACK FOUNDATION - PEDESTAL PLANS, SECTIONS AND RC DETAILS - SHEET 2 | 01 | Issued AFC (01) | Issued IFR (00) | Issued AFC (01) | 25/05/2022 | 19-Aug-22 | 01/12/2022 | 06-Apr-23 | |||||||||||||
| 16 | CPF - POWERGEN WBS-4100 - INLET FILTER HOUSE FOUNDATION - PLANS, SECTIONS AND RC DETAILS - SHEET 1 | 02 | Reissued AFC (02) | Issued IFR (00) | Reissued AFC (02) | 25/05/2022 | 19-Aug-22 | 01/12/2022 | 06-Dec-22 | 12-Jan-23 | ||||||||||||
| 17 | CPF - POWERGEN WBS-4100 - INLET FILTER HOUSE FOUNDATION - PEDESTAL PLANS, SECTIONS AND RC DETAILS - SHEET 2 | 02 | Reissued AFC (02) | Issued IFR (00) | Reissued AFC (02) | 25/05/2022 | 19-Aug-22 | 01/12/2022 | 06-Dec-22 | 12-Jan-23 | ||||||||||||
| 18 | CPF - POWERGEN WBS-4100 - LUBE OIL COOLER FOUNDATION - PLANS, SECTIONS AND RC DETAILS | 03 | Reissued AFC (03) | Issued IFR (00) | Reissued AFC (03) | 25/05/2022 | 26-Aug-22 | 01/12/2022 | 13-Jan-23 | 06-Feb-23 | 14-Apr-23 | |||||||||||
| 19 | CPF - POWERGEN WBS-4100 - ATOMIZING AIR COOLER FOUNDATION - PLANS, SECTIONS AND RC DETAILS | 03 | Reissued AFC (03) | Issued IFR (00) | Reissued AFC (03) | 25/05/2022 | 26-Aug-22 | 01/12/2022 | 13-Jan-23 | 06-Feb-23 | 14-Apr-23 | |||||||||||
| 20 | CPF - POWERGEN WBS-4100 - GTG TRANSFORMER FOUNDATION - PLAN, SECTIONS AND RC DETAILS | 00 | Issued IFR (00) | Issued IFR (00) | 25/05/2022 | 10-Mar-23 | 01/12/2022 | |||||||||||||||
| 21 | CPF - POWERGEN WBS-4100 - GTG TRANSFORMER WALL - PLAN, SECTIONS AND RC DETAILS | 00 | Issued IFR (00) | Issued IFR (00) | 25/05/2022 | 10-Mar-23 | 01/12/2022 | |||||||||||||||
| 22 | CPF - POWERGEN WBS-4100 - FUEL GAS SCRUBBER SKID FOUNDATION | 02 | Reissued AFC (02) | Issued IFR (00) | Reissued AFC (02) | 25/05/2022 | 13-Jan-23 | 01/12/2022 | 07-Feb-23 | 28-Mar-23 | ||||||||||||
| 23 | CPF - POWERGEN WBS-4100 - FIRE EXTINQUISHING BOTTLE SKID FOUNDATION | 01 | Issued AFC (01) | Issued IFR (00) | Issued AFC (01) | 25/05/2022 | 13-Jan-23 | 01/12/2022 | 07-Feb-23 | |||||||||||||
| 24 | CPF - POWERGEN WBS-4100 - HOT GAS PATH WASHING SKID FOUNDATION | 01 | Issued AFC (01) | Issued IFR (00) | Issued AFC (01) | 25/05/2022 | 13-Jan-23 | 01/12/2022 | 07-Feb-23 | |||||||||||||
| 25 | CPF -POWERGEN AREA CABLERACK 4100-EIT-001 AND 002 FOUNDATION PLANS - LOCATION PLAN - SHEET 1 | 00 | Issued IFR (00) | Issued IFR (00) | 25/05/2022 | 22-Feb-23 | 01/12/2022 | |||||||||||||||
| 26 | CPF - POWERGEN AREA CABLERACK 4100-EIT-001 AND 002 FOUNDATION PLANS - SECTION AND RC DETAILS - SHEET 2 | 00 | Issued IFR (00) | Issued IFR (00) | 25/05/2022 | 22-Feb-23 | 01/12/2022 | |||||||||||||||
| 27 | CPF - POWERGEN AREA CABLERACK 4100-EIT-001 AND 002 PEDESTAL PLANS - SECTION AND RC DETAILS - SHEET 3 | 00 | Issued IFR (00) | Issued IFR (00) | 25/05/2022 | 22-Feb-23 | 01/12/2022 | |||||||||||||||
| 28 | VOID - CPF - EIT BUILDING SUBSTATION NORTH FOUNDATION - RC DETAILS - SHEET 5 | NOT ISSUED | #N/A | 25/05/2022 | 01/12/2022 | |||||||||||||||||
| 29 | VOID - CPF - EIT BUILDING SUBSTATION NORTH FOUNDATION - RC DETAILS - SHEET 6 | NOT ISSUED | #N/A | 25/05/2022 | 01/12/2022 | |||||||||||||||||
| 30 | CPF - POWERGEN AREA PIPERACK 4100-PPR-001 TO 006 - FOUNDATION LOCATION PLAN - SHEET 1 | 01 | Issued AFC (01) | Issued IFR (00) | Issued AFC (01) | 25/05/2022 | 01-Jul-22 | 01/12/2022 | 06-Feb-23 | |||||||||||||
| 31 | CPF - POWERGEN AREA PIPERACK 4100-PPR-001 TO 006 - FOUNDATION PLANS, SECTIONS AND RC DETAILS - SHEET 2 | 01 | Issued AFC (01) | Issued IFR (00) | Issued AFC (01) | 25/05/2022 | 01-Jul-22 | 01/12/2022 | 06-Feb-23 | |||||||||||||
| 32 | CPF - POWERGEN AREA PIPERACK 4100-PPR-001 TO 006 - FOUNDATION PLANS, SECTIONS AND RC DETAILS - SHEET 3 | 01 | Issued AFC (01) | Issued IFR (00) | Issued AFC (01) | 25/05/2022 | 01-Jul-22 | 01/12/2022 | 06-Feb-23 | |||||||||||||
| 33 | CPF - POWERGEN AREA PIPERACK 4100-PPR-001 TO 006 - PEDESTAL PLANS, SECTIONS AND RC DETAILS - SHEET 4 | 01 | Issued AFC (01) | Issued IFR (00) | Issued AFC (01) | 25/05/2022 | 01-Jul-22 | 01/12/2022 | 06-Feb-23 | |||||||||||||
| 34 | CPF - POWERGEN AREA PIPERACK - 4100-FPR-001 TO 004 AND 4100-FPR-001a TO 004a - FOUNDATION LOCATION PLAN - SHEET 1 | 01 | Issued AFC (01) | Issued IFR (00) | Issued AFC (01) | 25/05/2022 | 01-Jul-22 | 01/12/2022 | 23-Mar-23 | |||||||||||||
| 35 | CPF - POWERGEN AREA PIPERACK - 4100-FPR-001 TO 004 AND 4100-FPR-001a TO 004a - FOUNDATION PLANS, SECTIONS AND RC DETAILS - SHEET 2 | 01 | Issued AFC (01) | Issued IFR (00) | Issued AFC (01) | 25/05/2022 | 01-Jul-22 | 01/12/2022 | 23-Mar-23 | |||||||||||||
| 36 | CPF - POWERGEN AREA PIPERACK - 4100-FPR-001 TO 004 AND 4100-FPR-001a TO 004a - PEDESTAL PLANS, SECTIONS AND RC DETAILS - SHEET 3 | 01 | Issued AFC (01) | Issued IFR (00) | Issued AFC (01) | 25/05/2022 | 01-Jul-22 | 01/12/2022 | 23-Mar-23 | |||||||||||||
| 37 | CPF - POWERGEN WBS-4100 - ELECTRICAL TRENCHES - PLAN - SHEET 1 | NOT ISSUED | #N/A | |||||||||||||||||||
| 38 | CPF - POWERGEN WBS-4100 - ELECTRICAL TRENCHES - ELEVATIONS AND DETAILS - SHEET 2 | NOT ISSUED | #N/A | |||||||||||||||||||
| 39 | CPF - POWERGEN WBS-4100 - ELECTRICAL TRENCHES - DETAILS SHEET 3 | NOT ISSUED | #N/A | |||||||||||||||||||
| 40 | #N/A | |||||||||||||||||||||
| 41 | #N/A | |||||||||||||||||||||
Civils-Fdns | ||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D3:D41 | D3 | =LOOKUP(2,1/(E3:F3<>""),E3:F3) |
| E3:E41 | E3 | =IF(COUNTA(I3:J3)=0,"",INDEX($I$1:$J$1,MATCH(LOOKUP(2,1/(I3:J3<>""),I3:J3),I3:J3,0))) |
| F3:F41 | F3 | =IF(COUNTA(L3:AD3)=0,"",INDEX($L$1:$AD$1,MATCH(LOOKUP(2,1/(L3:AD3<>""),L3:AD3),L3:AD3,0))) |
| C3:C39 | C3 | =IFERROR(HLOOKUP(D3,LOOKUPDATA!$A$4:$O$5,2,FALSE), "NOT ISSUED") |
| Drawing Issue Register v3.xlsx | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 1 | LOOKUP TABLE - DO NOT DELETE | ||||||||||||||||
| 2 | |||||||||||||||||
| 3 | |||||||||||||||||
| 4 | Not Issued | Planned IFR (00) Date | Issued IFR (00) | CPY comment (00) | Planned AFC (01) Date | Issued AFC (01) | CPY comment (01) | Reissued AFC (02) | CPY comment (02) | Reissued AFC (03) | CPY comment (03) | Reissued AFC (04) | CPY comment (04) | Reissued AFC (05) | CPY comment (05) | ||
| 5 | Not Issued | 00 | 00 | 00 | 01 | 01 | 02 | 02 | 03 | 03 | 04 | 04 | 05 | 05 | |||
LOOKUPDATA | |||||||||||||||||
