Hi,
This instruction is usually inserted into a macro ...
Are you already using a macro in your workbook ?
Hi,
This instruction is usually inserted into a macro ...
Are you already using a macro in your workbook ?
Quite honestly, even for Users who are not familiar with Excel, the basics of a Pivot Table are extremely simple ...
Among the possible solutions to completely redesign your Overview:
1. Have you considered Inserting a Pivot Table ?
or
2. Have you considered using the Advanced Filter feature ?
Well ... as an initial remark handling dates in Power Query can be extremely tricky ...
see following link :
In addition, the equivalent of Datediff in PQ is not exactly obvious ...
see following link:
OK ... Back to the basics ...
1. The Source of all your Data is "Production Reports"
2. The Destination is an Analysis in sheet "Overview"
Can you re-build this sheet so that it fully answers your needs ?
If Yes, can you explain all the aspects of your Objective ?
And
describe all the obstacles, complications you are facing ...
Hi and Welcome to the Forum
If you allow me a couple of initial remarks :
1. All macros do require Code Tags to allow Forum readers to properly access the VBA instructions ...
2. Attaching a sample test file would be far more effective ...given that it exposes your specific situation, and a file provides the exact framework in which all your macros do operate ...
Thanks a lot ...!!!
As soon as I have a moment, will look into the specifics of your situation ...
My understanding was that you wanted to add your column, your calculation, filter your records out ...BEFOREHAND ... during your initial process before using Power Query ...
Is it the case or not ?
Great ... !!!
However, this new approach will require to start all over and rebuild a solution from scratch ... especially if your are dealing with several workbooks ...
By the way, how many criteria are to be taken into consideration ...
it seems to me you have left the simplistic and trivial " 2-criteria-territory" ...
Hi,
Thanks for your sample file.
It appears that an approach based on formulas will not generate your expected result ... as you are adding more criteria ...
If need be, could explain the problems generated by Large(range,k) where k turns out to be the issue ... since it is no longer the same total of identical rows ...
Would you mind heading towards a faster and more reliable VBA-based solution ?
Hi,
If your question is related to designing the formula, you can test :
=yourSecondDate>=EDATE(yourFirstDate,7)
Hope this will help
You are welcome
You are welcome
Hi and Welcome to the Forum
If you are interested in a generic solution to compare two Columns and return differences, see following link :
Compare two columns and return differences
If you are looking for a customized solution adapted to your situation, thanks for attaching a small sample file
Glad to hear you have solved your problem
Thanks a lot for your Thanks AND for the Like
Any feedback ... ???
Hi,
You can test following formula
=IF(B10="";"";IF(COUNTIF('[April 2023 scale_Cycle count.xls]Scale'!$E$7:$K$37;B10)>0;COUNTIF('[April 2023 scale_Cycle count.xls]Scale'!$E$7:$K$37;B10)/4;IF(COUNTIF('[April 2023 scale_Cycle count.xls]Scale'!$AI$7:$BZ$37;B10)>0;COUNTIF('[April 2023 scale_Cycle count.xls]Scale'!$AI$7:$BZ$37;B10)/4;IF(COUNTIF('[April 2023 scale_Cycle count.xls]Scale'!$CA$7:$CD$37;B10)>0;COUNTIF('[April 2023 scale_Cycle count.xls]Scale'!$CA$7:$CD$37;B10)/4;""))))
Hi and Welcome to the Forum
Your question is not extremely clear :
1. If you need to update Slicers with a Macro :
2. If you need to convert a worksheet formula into VBA, you should consider Evaluate
Hope this will help
Hi again,
Properly used, Excel is simply Pure Magic
To discover the benefits of a flat file and adequately using Excel as a Database :
Enjoy ...
Hope this will help