Aviation CAD TechNotes

Technote: P-51 Mustang Drawing Register Updated

There seems to be a theme developing here…following on from my efforts to organise the chaos of large blueprint collections I endeavoured to continue my efforts with a long-overdue update to the P-51 Drawing register.

The P-39 Airacobra register was a breeze by comparison to this P-51. That was only a matter of 4-5 hours of work which was aided by the fact the drawing filenames were already fully described…all I had to do was add the Film Index numbers alongside the filenames. The P-51 on the other hand only had obscure filenames that were somewhat inconsistent…which meant this exercise ran into a few days. Occasionally my enthusiasm tends to thwart common sense!

Getting back to the P-51 Drawing register. The update is now inclusive of hyperlinks contained within the excel spreadsheets that will open the associated drawing. This is a huge step forward in managing and working with such a large archive and though it took ages it is a major improvement.

As you can see LINKS have been added to the right column (J) with hyperlinks recorded in column L. This column is hidden but can easily be viewed by using the option to UNHIDE. The Film Index reference is the actual microfilm reference hard coded onto the original film which differs from the actual filename that was generated when the film was scanned.

I should note at this stage that a number of folders in the archive will require renaming as Excel does not like #hashtags in naming conventions. The download section includes a word document describing the file-naming convention.

The hyperlinks are plain text entries originally copied from the development process that utilised the Vlookup function referencing a separate spreadsheet. I had considered including the separate spreadsheet in the download section but I think that just over-complicates things. About that development process!! It may be prudent to provide a quick overview of how things were developed.

The Development Process (briefly)

The initial process was to extract the filenames from Windows Explorer and deposit those records into a separate spreadsheet. The way to do this is to select all the files in the folder and click the Copy Path option in the windows explore toolbar.

Paste this into a spreadsheet and then remove the first part of the path (highlighted) so the location parameters now become a relative path to the root folder. This was done using the Replace (CTRL+H) function by copying the highlighted portion and applying a null space to all of the records.

This is now the actual hyperlink path which we need to associate with the actual Film Index. As mentioned above the Film Index is recorded on the scanned images and therefore a fair amount of manual intervention is required to record this value in column A. Using the Vlookup function necessitates that we use the column on the left for the value sought to return the value on the right. As you can see from the many tabs on this spreadsheet I filtered out all the filenames from every folder and then proceeded to populate the column in each case with the Film Index number…that drove me nuts!

There are several ways of accessing the values using Index and Match or even Indirect in conjunction with Vlookup…but we shall stick with the simple option of using Vlookup.

The Vlookup function asks for an initial lookup value; in this case “I10” then it asks for the corresponding Table Array; essentially the array of data from which to search. In this example, the array is defined as the values from the spreadsheet called “FILELIST” Tab “A” from cell A1 to B1043. The “2” refers to the column from which to extract the value you are seeking…which refers to the second column. “False” is for an exact match to the value in I10.

The Link is simply the =HYPERLINK function referencing the value in column K with a text value defining the label “LINK”.

You can combine the HYPERLINK function with the VLOOKUP in one formula like this…though it does take a fraction longer for the link to open.

That’s the basics of how this was done. using Indirect in conjunction with Vlookup enables you to search for the tab designation from a tab list that would look through the entire spreadsheet for the sought value. I didn’t think this was necessary for this exercise.

I mentioned the folder name changes that are required for this to work. The 3 main folders should now be changed to P-51 Mustang D01, D02 and D03.

The updated drawing registers will be available for download this evening so watch this space for an update.

As usual, the spreadsheets are fully editable so you can adapt the data to suit your own requirements. I would note that the Vlookup formulae are not embedded within these drawing registers as the hyperlinks are just copied text values and not live links. The recordset “FILELIST” is not available for download but if you would like a copy to play around with Vlookup or similar then please just drop me a line.

Update (earlier than expected)

The updated P-51 Mustang Drawing Registers are now online and available for download. Please let me know any comments or feedback.

This folder also includes the Aviation Manufacturers Standard Parts file which I am trying to consolidate as they tend to pertain to more than one aircraft.

Comments or feedback as usual to hughtechnotes@gmail.com