Can anyone explain in laymans terms what the R1C1 Formulas are actually doing?

I don't know what consititutes layman's terms, but here's how R1C1 references work.

R means rows, C means columns.

When the R1C1 option is selected in Excel options, row and column headers are both numbers. Rows are numbered from 1 to 1E6 from top to bottom. Columns are numbered 1 to 16000 from left to right (for most of us who are used to reading from left to right. In locales/languages where one is used to reading from right to left, it's backwards).

Numbers after R or C represent absolute references. R1C1 is an absolute reference to row #1, column #1, or cell $A$1 in A1 notation.

Numbers in brackets [] after R or C represent relative references. R[-1]C[-1] is a reference to the cell 1 row above and 1 column to the left of the cell containing the reference. You have to know which cell contains the reference in order to translate into A1 notation. Note that [0] means same row/column, and is usually omitted. R[0]C[-1] and RC[-1] mean the same thing (same row, 1 column to the left).

Mixed use of brackets/no brackets yields mixed absolute/relative references. RC4 refers the cell in column 4 (column D) of the same row. R3C[5] refers to the cell in row 3 of the column 5 columns to the right.

The formulas being entered by the code then mean:
in B2: "=IF(RC[-1]="""","""",'Track Data'!RC)" If the cell in the same row and one column to the left (A2) is null string (or blank), return null string, else return the value from "Track Data" same row and column (B2). Note that, if we made the first reference a mixed reference ($A2 or RC1), C2 is simply a copy of this formula.

In D2: "=IF(RC[-3]="""","""",'Track Data'!RC[2])" If the cell in the same row and 3 columns to the left (A2 again, or, looking at the other formulas, perhaps more appropriate RC1 or $A2 again) is null string, return null string, else return the value from "Track Data" in the same row and 2 columns to the right (F2). Again, if we made the IF(test) a mixed reference to always reference column 1/A, E2:H2 are just copies of D2.

At this point, I will observe that the main advantage to R1C1 notation when using VBA to write formulas is that the formula text doesn't change when we copy/paste/fill formulas in R1C1 notation. In A1 notation, of course, the formula text changes. The formula in B2, =IF(A2="""","""",'Track Data'!B2), when copied into B3, changes to =IF(A3="""","""",'Track Data'!B3). In R1C1 notation, however, the formula in B3 looks exactly like the formula in B2. =IF(RC[-1]="""","""",'Track Data'!RC). With the added observation that the IF tests are always referencing column A, we can replace all of that VBA code with 2 VBA statements:

Please Login or Register  to view this content.

I'm not sure exactly how you want to "add extra columns," but hopefully that help you see how these formulas work so you can figure out how to add more columns.

R1C1 formulas refer to a style of referencing cells in a spreadsheet

"R" stands for "row," and "C" stands for "column."
So, R1C1 means "Row 1, Column 1."
Each cell in a spreadsheet can be identified by its row number and column letter (or vice versa).
For example, R3C2 refers to Row 3, Column 2.
It's a different way of describing cell locations compared to the more common A1 style, where cells are referenced by their column letter and row number (like A1, B2, C3, etc.).