Forum Discussion

Gamer85308's avatar
Gamer85308
Copper Contributor
May 15, 2023
Solved

Need to further automate an Excel formula

Hello and thank you in advance. I am using Office 365 and here is the formula which needs more work. I need two things added to it. If you click on the image it will grow larger.

1. I need to wrap it in  ISERROR like as follows - =ISERROR(the formula below), "There are no Area One accounts requiring deactivation this month"). I've tried this and I get an error message which says I have too many arguments. How do I fix it?

2. I need to change these sections (Data!I5:I144<40000)*(Data!J5:J144<40000)*(Data!K5:K144<40000)*(Data!L5:L144<40000) as they are pointing at specific columns and I need them to update as the year goes on. For example, in May I get revenue data on the past four months Jan-April. In June I get it for the past five months Jan-May, etc. I need this section to update as the column headers in F6:I6 update. The column headers there use a simple formula like =TEXT(EDATE(TODAY(),-1),"Mmm")  which returns April in cell I6. H6 shows =TEXT(EDATE(TODAY(),-2),"Mmm") and etc. I need the formula to update to pull data from the correct column to match the month in the column headers. Does this make sense?

 

Thanks again for any help!

 

  • Gamer85308 

    The formula in C7 can be changed to

     

    =IFERROR(CHOOSECOLS(FILTER(Data!B5:T144,(Data!D5:D144=1)*(Data!I5:I144<40000)*(Data!J5:J144<40000)*(Data!K5:K144<40000)*(Data!L5:L144<40000),""),1,4,5,8,9,10,11),"")

7 Replies

  • Gamer85308 

    Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

    • Gamer85308's avatar
      Gamer85308
      Copper Contributor
      FYI, the formula in the image spills down and across
    • Gamer85308's avatar
      Gamer85308
      Copper Contributor
      @HansVogelaar
      I didn't know I could do that. My Excel has only first names, revenue numbers, and number of shipments. There is no sensitive data otherwise. Can I attach it here and then delete it later?
      • HansVogelaar's avatar
        HansVogelaar
        MVP

        Gamer85308 

        As for the date problem, if would be better if you used dates in the headers, formatted with the custom number format mmm. That would make it easier to use them in dynamic formulas.

Resources