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:I144<40000)*(Data!J5:J144<40000)*(Data!K5:K144<40000)*(Data!L5:L144<40000),""),1,4,5,8,9,10,11),"No accounts")The piece I need to improve is this:
Data!I5:I144<40000)*(Data!J5:J144<40000)*(Data!K5:K144<40000)*(Data!L5:L144<40000),"")The issue is that each section in the second code piece points at one column non-dynamically. For example, I5:I144 points to the month of January. Please see the image below. When May becomes June, the columns that now show Jan-April will change to Feb-May. I want the data below to change with it. How do I do this? I tried using XMATCH but it would not accept the "<40000" piece. Any suggestions?
Thank you again for any help
- 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")- 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"))
- 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!
