Create Dynamic Formula

**mbsmeltzer** · 02-10-2011, 01:36 PM

Follow the formula in B16 and C16. I'm looking for a VBA or fancy formula to continue that pattern into D16, etc. without having to click on every cell required to get the total of "33"

**DonkeyOte** · 02-10-2011, 02:20 PM

Welcome to the Board.

Note (given first post) I changed your title to something a little more appropriate and in keeping with Forum Rules - going forward please bear the requirements in mind.

Formula wise:

B16:
=SUMPRODUCT(N(INDIRECT(ADDRESS(12-(COLUMN(B16:$K16)-COLUMN(B16)),COLUMN(B16:$K16)))))+SUMPRODUCT(N(INDIRECT(ADDRESS(COLUMN($B16:B16),2+COLUMN(B16)-(COLUMN($B16:B16)),,,"Sheet2"))))
copied across to K16

however, the above is and volatile and brute force - moreover though it generates the correct results for your sample I'm not entirely sure it will be the long term solution.

**mbsmeltzer** · 02-10-2011, 04:51 PM

Wow! That is an amazing formula to look at. Thank you very much. I've transfered it into my spreadsheet at C45 in the Jan 11 tab. I'm having a hard time understanding how you pick up data from the Feb 11 tab in C8. I've changed the formula the way I think it should work, but is't not counting that last number from the Feb 11 tab.

Thank you so much for your help on this!

**DonkeyOte** · 02-10-2011, 05:05 PM

I am in the middle of cooking so time is limited... given sample does the below work for you in the meantime ?

'Jan-11'!C45
=SUMPRODUCT(N(INDIRECT(ADDRESS(40-(COLUMN(C45:$AG45)-COLUMN(C45)),COLUMN(C45:$AG45)))))+SUMPRODUCT(N(INDIRECT(ADDRESS(8+COLUMN($C45:C45)-COLUMN($C45),3+COLUMN(C45)-(COLUMN($C45:C45)),,,"Feb 11"))))
copied across

I will post back with a more generic/transparent solution in due course

**mbsmeltzer** · 02-10-2011, 05:29 PM

Indeed it does! I love the part about you cooking. Makes me think of the difference between me staring at that formula for hours and you hammering it out in a minute flat! Thank you

**DonkeyOte** · 02-10-2011, 07:21 PM

This is perhaps slightly more transparent

=SUMPRODUCT(N(INDIRECT(ADDRESS(ROW(C$40)-(COLUMN(C40:$AG40)-COLUMN(C40)),COLUMN(C40:$AG40)))))+SUMPRODUCT(N(INDIRECT(ADDRESS(ROW(C$8)+COLUMN($C8:C8)-COLUMN($C8),COLUMN($C8)+COLUMN(C8)-(COLUMN($C8:C8)),,,TEXT(DATE(YEAR($A$1),MONTH($A$1)+1,1),"mmm yy")))))

The 1st SUMPRODUCT relates to current sheet matrix and starts from bottom left (up & left to right)

The 2nd SUMPRODUCT relates to next sheet matrix and starts effectively from top row (down & right to left)

The references to C8 should really relate to Feb 11 but I think it safe to assume each monthly matrix to be identical.

Extending the above assumption further...

You will note in the above that the sheet reference (mmm yy) is now dynamic also (based off date in A1).

Result of this should be that you could apply the same formula to all monthly sheets (bar last) simultaneously without need for alteration in any way (other than modifying excess days)

Be aware INDIRECT is Volatile and SUMPRODUCTs are not very efficient so these calcs may slow performance.

On a final note there's probably a better approach - perhaps tomorrow will bring inspiration (assuming not forthcoming from others in the meantime....)