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 don't understand the question because it is unclear what you mean by "when may becomes June, the columns that now show Jan-April will change to Feb-May" and what you need the formula to change to. That said however, here is an idea/concept that might help. You already use the CHOOSECOLS() function and you can use that instead of the direct reference so you can then have a parameter like 'offset' that picks which column to start the check.
=IFERROR(CHOOSECOLS(FILTER(Data!B5:T144,(Data!D5:D144=1)*(CHOOSECOLS(Data!I5:M144,0+offset)<40000)*(CHOOSECOLS(Data!I5:M144,1+offset)<40000)*(CHOOSECOLS(Data!I5:M144,2+offset)<40000)*(CHOOSECOLS(Data!I5:M144,3+offset)<40000),""),1,4,5,8,9,10,11),"No accounts")
=IFERROR(CHOOSECOLS(FILTER(Data!B5:T144,(Data!D5:D144=1)*(CHOOSECOLS(Data!I5:M144,0+offset)<40000)*(CHOOSECOLS(Data!I5:M144,1+offset)<40000)*(CHOOSECOLS(Data!I5:M144,2+offset)<40000)*(CHOOSECOLS(Data!I5:M144,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- 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"))- 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
- Hello again mtarler, So I have four columns headers that look like this:
Jan Feb Mar Apr
The formula in the April column looks like this: =TEXT(EDATE(TODAY(),-1),"Mmm")
The formula in the Mar column looks like this: =TEXT(EDATE(TODAY(),-2),"Mmm")
The formula in the Feb column looks like this: =TEXT(EDATE(TODAY(),-3),"Mmm")
The formula in the Jan column looks like this: =TEXT(EDATE(TODAY(),-4),"Mmm")
The April column is this month minus one so when May becomes June, April will become May on the column header.
What does +OFFSET do? I'll play with it. Thanks for your help!
