• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Why doesnt the formula work now but worked last month ?? VSTACK FILTER

shili12

shili12

Member
Once again , my sincere apologies for not posting a very large 200mb xlsx file, but i suppose you would only look at the formula and discern,
every month i delete a sheet for prior month and add a sheet for the new month, and update formula correspondingly.
however this month, am getting a hitch, the 2 formula that worked for last month are not working this month,
i even copy pasted the formula that worked and changed month names, to no effect.
Any explanations ? apr2023 is ??, may 2023 is ?? but they worked last month. feb2024 worked, now not working, march2024 not working. Removing apostrophes did not help for apr2023 and may2023, rest of months are ok.
the full formula
= IFERROR(VSTACK(FILTER('apr2023'!$A$2:$X$100000,ISNUMBER(SEARCH($A1,'apr2023'!$G$2:$G$100000)),"??"),FILTER('may2023'!$A$2:$X$100000,ISNUMBER(SEARCH($A1,'may2023'!$G$2:$G$100000)),"??"),FILTER(june2023!$A$2:$X$100000,ISNUMBER(SEARCH($A1,june2023!$G$2:$G$100000)),"??"),FILTER(july2023!$A$2:$X$100000,ISNUMBER(SEARCH($A1,july2023!$G$2:$G$100000)),"??"),FILTER('aug2023'!$A$2:$X$100000,ISNUMBER(SEARCH($A1,'aug2023'!$G$2:$G$100000)),"??"),FILTER(sept2023!$A$2:$X$100000,ISNUMBER(SEARCH($A1,sept2023!$G$2:$G$100000)),"??"),FILTER('oct2023'!$A$2:$X$100000,ISNUMBER(SEARCH($A1,'oct2023'!$G$2:$G$100000)),"??"),FILTER('nov2023'!$A$2:$X$100000,ISNUMBER(SEARCH($A1,'nov2023'!$G$2:$G$100000)),"??"),FILTER('dec2023'!$A$2:$X$100000,ISNUMBER(SEARCH($A1,'dec2023'!$G$2:$G$100000)),"??"),FILTER('jan2024'!$A$2:$X100000,ISNUMBER(SEARCH($A1,'jan2024'!$G$2:$G$100000)),"??"),FILTER('feb2024'!$A$2:$Y100000,ISNUMBER(SEARCH($A1,'feb2024'!$A$2:$A$100000)),"??"),FILTER('mar2024'!$A$2:$Y100000,ISNUMBER(SEARCH($A1,'mar2024'!$A$2:$A$100000)),"??")),"??")
Click to expand...

question.png
 
Hello

At a quick glance i can see
there might be a typo in your formula.
In the last two FILTER functions for 'feb2024' and 'mar2024', the range reference seems incorrect.
Instead of '$A$2:$A$100000', it should probably be '$G$2:$G$100000' to match the structure of the previous FILTER functions. Try correcting that and see if it resolves the issue.
 
Why those long formulas, I wonder?

You have clearly a modern version of Excel if not 365 since you have filter and vstack.
Consolidating sheets is rather straightforward with Power Query and using a dynamic filter on current/previous month is build in too.
No need for manual delete and insert of new sheet with updating formulas.
Sounds so much easier to do a simple "refresh". Which also can be automated, btw.
 
Monty said:
Hello

At a quick glance i can see
there might be a typo in your formula.
In the last two FILTER functions for 'feb2024' and 'mar2024', the range reference seems incorrect.
Instead of '$A$2:$A$100000', it should probably be '$G$2:$G$100000' to match the structure of the previous FILTER functions. Try correcting that and see if it resolves the issue.
Click to expand...
it solved the 2nd part but in spite of several attempts, for the first part couldn't do it even format was general for all columns, i may go power query way as my learning curve picks.
 
right, there is also an issue with may 2023 as its a table that refuses to be deleted, it was fine last month. sorry for sending pic, the xlsx file is like 194 MB, but you can discern.
question.png
 
You must log in or register to reply here.
Back
Top