0

Can you please explain different components of this formula to me ? I am trying to summarize data from 12 monthly worksheets to one worksheet and need to tweak this to work. Thanks

=INDIRECT("'T2."& MONTH(\$A16 & 0) & " Allocation " & LEFT(\$A16, 3)& "'!" & ADDRESS(MATCH(G\$14,'T2.2 AllocationFeb'!\$B:\$B,0),MATCH(\$A\$13,'T2.2 Allocation Feb'!\$6:\$6,0)))

0

In regards to this formula, it is creating a dynamically generated cell reference. This is a pretty advanced formula and here are the key points:

It is trying to find what is in cell G14 and A13 on the AllocationFeb sheet and will then return the location of that data back to the formula while combining it with manipulated data from cell A16, using the LEFT() function to get the first three characters from the cell and the MONTH() function to get the number of the month from cell A16, which, presumably, contains a text representation of the month.

And the INDIRECT() function is what combines everything into a usable cell reference.

An easier way to try to understand it is to select the cell with the formula and go to the Formulas tab and click the button Evaluate Formula within the Formula Auditing section.