Forum Discussion
Solved
Need to alter a formula to dynamically look for data in a column based on month selected
Hello and thanks in advance for any help. I am using Office 365. I have this formula which works well but I need to improve it: =IFERROR(CHOOSECOLS(FILTER(Data!B5:T144,(Data!D5:D144=1)*(Data!I5...
- I still don't get what/why you are doing this. The "offset" I put in just as a place holder. so if you want it to always point to 'Apr' column then maybe use Match(). or you can base it on the Month in cell D4.
You can use LET() to make it easier if you want so then you 'define' offset in the let statement and then use it in the rest. I am not claiming the following Match is the right value for the offset, just an idea for you to work with:
=LET(offset, Match(text(edate(today(),-1),"Mmm"),Data!G4:T4,0),
IFERROR(CHOOSECOLS(FILTER(Data!B5:T144,(Data!D5:D144=1)*(CHOOSECOLS(Data!G5:T144,0+offset)<40000)*(CHOOSECOLS(Data!G5:T144,1+offset)<40000)*(CHOOSECOLS(Data!G5:T144,2+offset)<40000)*(CHOOSECOLS(Data!G5:T144,3+offset)<40000),""),1,4,5,8,9,10,11),"No accounts"))
I added the formula as follows and I had to edit some of the cell references
=IFERROR(CHOOSECOLS(FILTER(Data!B5:T144,(Data!D5:D144=1)*(CHOOSECOLS(Data!G5:T144,0+offset)<40000)*(CHOOSECOLS(Data!G5:T144,1+offset)<40000)*(CHOOSECOLS(Data!G5:T144,2+offset)<40000)*(CHOOSECOLS(Data!G5:T144,3+offset)<40000),""),1,4,5,8,9,10,11),"No accounts")
It is returning No accounts so something is wrong
=IFERROR(CHOOSECOLS(FILTER(Data!B5:T144,(Data!D5:D144=1)*(CHOOSECOLS(Data!G5:T144,0+offset)<40000)*(CHOOSECOLS(Data!G5:T144,1+offset)<40000)*(CHOOSECOLS(Data!G5:T144,2+offset)<40000)*(CHOOSECOLS(Data!G5:T144,3+offset)<40000),""),1,4,5,8,9,10,11),"No accounts")
It is returning No accounts so something is wrong
I still don't get what/why you are doing this. The "offset" I put in just as a place holder. so if you want it to always point to 'Apr' column then maybe use Match(). or you can base it on the Month in cell D4.
You can use LET() to make it easier if you want so then you 'define' offset in the let statement and then use it in the rest. I am not claiming the following Match is the right value for the offset, just an idea for you to work with:
=LET(offset, Match(text(edate(today(),-1),"Mmm"),Data!G4:T4,0),
IFERROR(CHOOSECOLS(FILTER(Data!B5:T144,(Data!D5:D144=1)*(CHOOSECOLS(Data!G5:T144,0+offset)<40000)*(CHOOSECOLS(Data!G5:T144,1+offset)<40000)*(CHOOSECOLS(Data!G5:T144,2+offset)<40000)*(CHOOSECOLS(Data!G5:T144,3+offset)<40000),""),1,4,5,8,9,10,11),"No accounts"))
You can use LET() to make it easier if you want so then you 'define' offset in the let statement and then use it in the rest. I am not claiming the following Match is the right value for the offset, just an idea for you to work with:
=LET(offset, Match(text(edate(today(),-1),"Mmm"),Data!G4:T4,0),
IFERROR(CHOOSECOLS(FILTER(Data!B5:T144,(Data!D5:D144=1)*(CHOOSECOLS(Data!G5:T144,0+offset)<40000)*(CHOOSECOLS(Data!G5:T144,1+offset)<40000)*(CHOOSECOLS(Data!G5:T144,2+offset)<40000)*(CHOOSECOLS(Data!G5:T144,3+offset)<40000),""),1,4,5,8,9,10,11),"No accounts"))
- The why. I created this for my team and I know how to work it. I will be handing it to three other teams and each copy will be managed by someone on that team who has no Excel experience or not much at all. Gathering the data for the data and shipments tabs takes me about two hours. If I have to ask them to update cells every month I don't believe they will do it. The harder I make it, the less likely it is they will use it. The value to the teams cannot be overstated and I have to automate this as much as possible so it won't overwhelm clueless users who manage it.
I don't understand how inserting the word "offset" in the formula helps at all but I will mark this as solved as I am not making any headway here and no one else is offering any better solutions- I get it, I'm reading about LET
- Yes, LET is VERY powerful and VERY useful. Definitely learn LET and then learn LAMBDA and change this to be a LAMBDA function so in each cell you can something easy like =MyFunction()
BTW, in hindsight the name "offset" was probably a poor choice by me since it is a built in Excel function name already.