# Code to Update Monthly Sheets with Formulas

• ### Question

• Good day,

I have 2 data sheets a sales sheet and a booking sheet.

On the Data Sales sheet I have concatenated the agency and the month (column L) as well as the agency and the type of deal (column M).  I also use column H (Purchase Price) and K (Agency Name).

I have different month sheets (Jan, Feb, Mar etc.) I have recorded a Macro to update those months with the data in the data sheets.  I have one button to update all months, but I have added columns and other coding and now it doesn't calculate at all.  I need help with the coding so that I don't have to record it as that makes it very difficult to amend when there are changes to be made.

On the Jan tab, I have the agency name in B11, the type of deal in D7 (Solid), and the month in B7 (January).

My formula in C11 is =countifs('Data Sales'!\$E:\$E;'Jan'!\$D\$7;'Data Sales'!\$L:\$L;concatenate('Jan'!\$B11;Jan\$B\$7))

My formula in D11 is =sumifs('Data Sales'!\$H:\$H;'Data Sales'!\$M:\$M;concatenate('Jan'!\$B\$7;'Jan'!\$D\$7);'Data Sales'!\$K:\$K;'Jan'!\$B11)

On the Data GB sheet I have concatenated the Agency and whether they are qualified in column J as well as the agency whether they are qualified and if they were pitched in column K.

I also want that to update on all the month tabs.

On the Jan tab, I have the month and a Y in column W7 (JanuaryY), and the month and YYes in column X7 (JanuaryYYes).

My formula in W11 is = countif('Data GB'!\$J:\$J;concatenate('Jan'!\$B11;'Jan'!\$W\$7)

My formula in X11 is = countif('Data GB'!\$K:\$K;concatenate('Jan'!\$B11;'Jan'!\$X\$7)

If someone can please assist with that.

Hoping to hear from you soon.

Thank you

Thursday, November 10, 2016 8:25 AM

### All replies

• For each month the formulas will be in the same cell on each tab.
Thursday, November 10, 2016 8:26 AM