Convert file equations into code

[Hany ali]

Hello My Dear I want Your Help to Convert Equations for fm 01.05 till 31.05 Sheet and Every month a new one will be opened in the future ,which in columns start From A till E
This is because the file has become very heavy and it is not easy to handle. Thank you very much

=LET(data1,'Nefertity For Natural Oils'!$A$3:$D$2084,data2,'City Tour'!$A$3:$D$1129,filterdata1,FILTER(data1,(INDEX(data1,0,1)>=$G$1)*(INDEX(data1,0,1)<=$H$1)),udata1,UNIQUE(CHOOSECOLS(filterdata1,1,4)),filterdata2,FILTER(data2,(INDEX(data2,0,1)>=$G$1)*(INDEX(data2,0,1)<=H1)),udata2,UNIQUE(CHOOSECOLS(filterdata2,1,4)),VSTACK(udata1,udata2))

=IF($A4="","",IF(ROW($A1)<=ROUND(SUMPRODUCT((('Nefertity For Natural Oils'!$A$3:$A$2084>=$G$1)*('Nefertity For Natural Oils'!$A$3:$A$2084<=$H$1)*('Nefertity For Natural Oils'!$A$3:$A$2084<>""))/COUNTIFS('Nefertity For Natural Oils'!$A$3:$A$2084,'Nefertity For Natural Oils'!$A$3:$A$2084&"",'Nefertity For Natural Oils'!$D$3:$D$2084,'Nefertity For Natural Oils'!$D$3:$D$2084&"")),0),SUMIFS('Nefertity For Natural Oils'!$I$3:$I$2084,'Nefertity For Natural Oils'!$A$3:$A$2084,$A4,'Nefertity For Natural Oils'!$D$3:$D$2084,$B4),SUMIFS('City Tour'!$J$3:$J$1129,'City Tour'!$A$3:$A$1129,$A4,'City Tour'!$D$3:$D$1129,$B4)))

 

[Hany ali]

Hello.... Up Please

 

[Marc L]

Hello, without VBA : just use a template worksheet with an 'empty' Excel table with formulas.​

When starting a new month, copy this template worksheet and rename the new worksheet according to this new month.​

Once the month is done, select the formulas cells, Copy then Paste as values in order to remove the formulas …​

​

 

[Hany ali]

thanks alot for your replay .how to make it .and I CAN'T WORK By All This Formulas file has become very heavy and very Slowly

 

[Marc L]

Like I wrote start to replace formulas with values for each achieved month​

and with big data avoid also conditional formatting using formulas …​

​

 

[Hany ali]

hello ,thanks with Best Regards

 

[Hany ali]

Hello ... Up Please

 

[p45cal]

This macro acts on the active sheet, so beware:

Sub blah()
Set rngtoclear = Intersect(ActiveSheet.UsedRange, Range("A:B"))
rngtoclear.Resize(rngtoclear.Rows.Count - 1).Offset(1).ClearContents
Range("A2").Formula2R1C1 = "=LET(data1,'Nefertity For Natural Oils'!R3C1:R2084C4,data2,'City Tour'!R3C1:R1129C4,filterdata1,FILTER(data1,(INDEX(data1,0,1)>=R1C7)*(INDEX(data1,0,1)<=R1C8)),udata1,UNIQUE(CHOOSECOLS(filterdata1,1,4)),filterdata2,FILTER(data2,(INDEX(data2,0,1)>=R1C7)*(INDEX(data2,0,1)<=R[-1]C[1])),udata2,UNIQUE(CHOOSECOLS(filterdata2,1,4)),VSTACK(udata1,udata2))"
Set Rng = Range("A2#")
Rng.Value = Rng.Value
End Sub

It clears columns A and B (except for row 1), put's your formula into cell A2, then converts the formulae results into plain values.

 

[Hany ali]

thanks alot for excellent answer ,it's work as well .But Please if You Can add else Column C formula In This Code

=IF($A8="","",IF(ROW($A1)<=ROUND(SUMPRODUCT((('Nefertity For Natural Oils'!$A$3:$A$2084>=$G$1)*('Nefertity For Natural Oils'!$A$3:$A$2084<=$H$1)*('Nefertity For Natural Oils'!$A$3:$A$2084<>""))/COUNTIFS('Nefertity For Natural Oils'!$A$3:$A$2084,'Nefertity For Natural Oils'!$A$3:$A$2084&"",'Nefertity For Natural Oils'!$D$3:$D$2084,'Nefertity For Natural Oils'!$D$3:$D$2084&"")),0),SUMIFS('Nefertity For Natural Oils'!$I$3:$I$2084,'Nefertity For Natural Oils'!$A$3:$A$2084,$A8,'Nefertity For Natural Oils'!$D$3:$D$2084,$B8),SUMIFS('City Tour'!$J$3:$J$1128,'City Tour'!$A$3:$A$1128,$A8,'City Tour'!$D$3:$D$1128,$B8)))

 

[p45cal]

That formula looks mighty long and complicated. Can you say in words what it's supposed to be doing?

 

[Hany ali]

Thanks Alot ,,We get the total amount against the Date and the Guide name from Nefertity For Natural Oils Sheet and City Tour Sheet Severally

 

[p45cal]

Check out the sheet fm 01.05 till 31.05 where there's a button to run a macro blah.
The macro works on the active sheet so be careful.
The columns are rearranged because it's difficult (impossible?) to have a formulae produce results in non-contiguous columns.
I'll leave you to reinstate the other columns.
Do check the values very carefully. I haven't.

Since this macro only puts 2 formulae into two cells, you may find that this is not terribly heavy and may not slow down the computer. This might mean that you can keep the formulae in place as formulae (at the moment the macro replaces the formulae with their results' plain values). To do that, you can take out (delete) the following part of the macro:

With Range("A2#").Resize(, 4)
  .Value = .Value
End With

The advantage to doing this is that the results will update themselves immediately the source data changes, without needing to rerun the macro.

 

[Hany ali]

ok thanks I will try to Make It ....Thank you very much for all this help

 

[Hany ali]

I apologize very much, the code works fine ..But If Possible to Change Shop Column to be F instead Of C ,and Total Sales To be C instead Of D
Because I Try To Change in This Code ,But my Attempts were unsuccessful
With my many thanks and gratitude to you, I wish you more happiness always

 

[p45cal]

to Change Shop Column to be F instead Of C ,and Total Sales To be C instead Of D

Not straightforward. Why do you want this? It should be easy for you to adjust other columns' formulae.
I'm reluctant to spend time and energy on what I see to be just a preference rather than a necessity.
I may have a look at it next week sometime. In the meantime, others may jump in and help.

 

[Hany ali]

Thank you very much for all the help you provided me, but this is really necessary for me because this is the same order as the pages from which I migrated. In general, I can wait for you until the time comes to provide assistance, or as I said, until one of the professors intervenes to help.