Counting up attachments using IF . . . THEN
Tonight's tip explains how to use the IF . . . THEN formula to add a new document number each time an attachment is referenced in a file name column. If we being with data like this:
. . . and we want to add a formula in column D which will lead to this result:
We can enter this formula in cell D2 and pull it down using CTRL + D
=IF(LEFT(C2,9)="file name",LEFT(C1,15)&".0001"&".pdf",C2)
This will only work if the attachments are consistently identified in column C with a file name beginning with 'File name', and you have one attachment for each parent document.
If you have more than one attachment, add a helper column in column E to get a count of the number of attachments for each document. In cell E2 enter:
=IF(LEFT(C2,9)<>"file name",0,E1+1)
This IF . . . THEN formula checks to see if the first 9 characters in cell C2 are not equal (<>) to 'file name', and returns 0 if they are not, marking each parent document. If there is an attachment referenced in cell C2, it instead adds 1 to the value already entered in cell E1. Assuming that the worksheet has the metadata sorted so that each parent document appears before each of the child documents associated with it, this will tell us which number each document is in the family.
In column D we can then enter this updated formula:
=IF(LEFT(C2,9)="file name",LEFT(D1,15)&".000"&E2&".pdf",C2)
. . . which will give us these results:
The IF . . . THEN formula looks for instances where the adjacent cell begins with 'file name', and if it does enters the first 15 characters from the cell above (the parent file name minus the file extension), three leading zeroes, and the attachment number referenced in column E. If column C indicates that it's a parent and not an attachment, the formula simply enters the value in cell C2.
The IF . . . THEN formula looks for instances where the adjacent cell begins with 'file name', and if it does enters the first 15 characters from the cell above (the parent file name minus the file extension), three leading zeroes, and the attachment number referenced in column E. If column C indicates that it's a parent and not an attachment, the formula simply enters the value in cell C2.