Code to Update Monthly Sheets with Formulas RRS feed

  • 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