Auto Refresh Pivot Table on Activate Worksheet

[Zack Barresse]

='Source Data'!$A$1:

Obviously starts at A1.

Array Reference:
INDEX('Source Data'!$A:$F,

This is the range that were going to look at. If our row or column numbers from hte next two sections are out of this range, formula will #REF! out.

Row Reference:
MATCH(REPT("z",255),'Source Data'!$A:$A),

This is the magic part, and it's far too difficult to explain, so layman's terms will suffice. It's basically a binary search down column A for the last cell filled with text. With the MATCH function, this returns reference numbers, a row number in this case. If we started on A2, we'd have to add 1 to this specific section or we'd be missing the very last bottom row. For finding numbers you'd use MATCH(9.99999999E+307,$A:$A), or for both use MAX(MATCH(9.99999999E+307,$A:$A),MATCH(REPT("z",255),'Source Data'!$A:$A)). This is especially helpful when you have data and want the last cell but do not know if it will be textual or numerical.

Column Reference
6)

Or column F in other words. If this was above 6, with the Array set to what it is (A:F) the formula would error out.

So on the numerical side, MATCH(9.99999999E+307,$A:$A) will bring back the last numerical cell in column a. Both the numerical and textual (9.9999, rept("z)) give the largest value you can use in excel, or the last cell in a specified range, searching from the bottom up.

If there is nothing in the column in question for the binary match portion, you'll receive the #N/A error. Depending on your data you can error handle this.

Hope that helps.

[Brandtrock]

Nice bit of work Zach! I have always used the COUNTA method, but I like the way yours works better.

[K. Georgiadis]

='Source Data'!$A$1:

Obviously starts at A1.

Array Reference:
INDEX('Source Data'!$A:$F,

This is the range that were going to look at. If our row or column numbers from hte next two sections are out of this range, formula will #REF! out.

Row Reference:
MATCH(REPT("z",255),'Source Data'!$A:$A),

This is the magic part, and it's far too difficult to explain, so layman's terms will suffice. It's basically a binary search down column A for the last cell filled with text. With the MATCH function, this returns reference numbers, a row number in this case. If we started on A2, we'd have to add 1 to this specific section or we'd be missing the very last bottom row. For finding numbers you'd use MATCH(9.99999999E+307,$A:$A), or for both use MAX(MATCH(9.99999999E+307,$A:$A),MATCH(REPT("z",255),'Source Data'!$A:$A)). This is especially helpful when you have data and want the last cell but do not know if it will be textual or numerical.

Column Reference
6)

Or column F in other words. If this was above 6, with the Array set to what it is (A:F) the formula would error out.

So on the numerical side, MATCH(9.99999999E+307,$A:$A) will bring back the last numerical cell in column a. Both the numerical and textual (9.9999, rept("z)) give the largest value you can use in excel, or the last cell in a specified range, searching from the bottom up.

If there is nothing in the column in question for the binary match portion, you'll receive the #N/A error. Depending on your data you can error handle this.

Hope that helps.

it most certainly helps! Thanks for the detailed explanation.