Forum Discussion
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!
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
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?
- Gamer85308Copper ContributorFYI, the formula in the image spills down and across
- Gamer85308Copper 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?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.