Updating workbook formulas for additional variables

PMDJunqueira, that is very interesting. A few questions.

1. How did you do this? :-)
2. How can I expand the Summary worksheet to add more rows?
3. I need to work in more variables F2 & H2 on the Summary worksheet.

But this is pretty awesome, never worked with whatever you did!

Thank you,

Originally Posted by nickpavlov

PMDJunqueira, that is very interesting. A few questions.
1. How did you do this? :-)
2. How can I expand the Summary worksheet to add more rows?
3. I need to work in more variables F2 & H2 on the Summary worksheet.
But this is pretty awesome, never worked with whatever you did!
Thank you,

All of this is because the new feature of Excel 365 to work with dynamic arrays.

1. You don't need to have and carry 1,000 of formulas any more, just one cell has it all!
2. There is no limit!!
3. I have already implemented that.

Look again the file, I am improving it in the first massage since I wasn't sure you where seeing it.

Now I implemented a Pivot Table with Timeline just for you to see the potential of Excel that you are not using.

Thank you DJ! This will keep me busy playing with for a week or two :-)

Thank you, happy to help.

Hi DJ,

I am playing around with the Table Filter Formula you created. (attached)

If I remove the dates in B2 & D2, I get a #CALC! error, since there is no date range.

Do you know a way that if there are no dates in those cells that it will display data from the last 180 days by default?

I started watching some YouTube videos on Pivot Tables to understand how you created those also, that will definitely keep me busy for a couple weeks

Thank you,

Hi nickpavlov,

I did some changes, now you can use the TRACKBACK again and for the date question I offer the solution of choosing instead the starting date of the Data table and the Today's date as limits that you can choose in the drop down menu, those dates do not limit the entry of other dates, it is just for not using blank as a criteria.

I also changed the order of the first two columns to their original places.

Thank you DJ, this is pretty exciting stuff for me, all the formulas before would slow down the processing.

Yes, this new version of Excel is very interesting, much easy to work with big data.

I tweaked it a bit more, I didn't really need a track back. But I do need by default to display the last 180 days.

So I have added a start date and end date that goes back 180 days, and for a custom date, the user can type in that.

What I also like about this technique is that you don't need to worry about dozens of pages printing due to hidden formulas.

Yes, the hidden formulas were very unproductive, growing excessively the size of the files.

DJ, do you know if it is possible when using the Filter Table Formula, can you not displays a column in a Table?

Example attached.

You can avoid displaying a column in a formula with FILTER().

I'm looking online and saw this formula example, where 4,3,5 is the columns displayed. I can't get it to work with the one you created.

Do you think it is something similar?

=FILTER(INDEX(tblData,SEQUENCE(ROWS(tblData)),{4,3,5}),tblData[Customer Name]=I3)

Originally Posted by nickpavlov

I'm looking online and saw this formula example, where 4,3,5 is the columns displayed. I can't get it to work with the one you created.
Do you think it is something similar?
=FILTER(INDEX(tblData,SEQUENCE(ROWS(tblData)),{4,3,5}),tblData[Customer Name]=I3)

The formula was there for you to play.

=IF(C1="";"";INDEX(FILTER(TData;IF(C1="All";1;TData[EMPLOYEE NAME]=C1)*(TData[AUDIT DATE]>=B2)*(TData[AUDIT DATE]<=D2)*IF(F2="All";1;TData[SHIFT]=F2)*IF(H2="All";1;TData[TYPE]=H2));SEQUENCE(IF(F1;F1;H1);1;IF(F1;H1-F1)+1;1);{2;1;3;4;5;6}))

Thanks DJ, missed it. That is much better than using 1,1,1,1,0,1,1 etc.

Update, watched some YouTube on this lol, this worked:

=IF(B2="","",FILTER(FILTER(TData,IF(B2="All",1,TData[EMPLOYEE NAME]=B2)*(TData[AUDIT DATE]>=C3)*(TData[AUDIT DATE]<=C4)*IF(E3="All",1,TData[SHIFT]=E3)*IF(E4="All",1,TData[TYPE]=E4)*IF(E2="All",1,TData[REASON]=E2)),{1,1,1,1,0,1,1}))