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")
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!
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!