Automating a formula with a macro

K0st4din

K0st4din

Active Member
Joined
Feb 8, 2012
Messages
488
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
Hello, everyone!
Friends, I ask for your cooperation, in my opinion, for some macro to replace the formulas I am working with at the moment.
I have two worksheets with tables in them - IME SLUJITEL and PREDSTAVITELNI.
In SHEET PREDSTAVITELNI, I try in column B of the table, with drop-down menus to export specific words that I need. These words are in column Q (and new words are added each month). In column H - money is applied.
In the worksheet IME SLUJITEL in column G (in the table), I use a formula which if it sees the words written in the other worksheet in column B (in the table) returns or if the date is the same, then according to the words returns the monetary value or sum of the sum , because the date is the same. (In the example, for example, the date is 14.6.2021 and the sum is 35.
So far, I manage by hand in column G in the formula to add each word by hand so that the corresponding amounts can be obtained according to the date.
What makes it difficult for me and I do not know how to do it - is how with a macro, if I have to add a new word in PREDSTAVITELNI in column Q, to add it automatically so I can avoid this insertion by hand.
I very much hope that I have explained everything understandable.
Please, if you have any misunderstandings, ask so I can help with more clarifications.
Thank you in advance for everyone's cooperation.

TEST AUTO ADD.xlsm
ABCDEFG
1
2на
3МПС рег.№
420216за времето от1.6.2021до30.6.2021
5
6dayformTravel
7monlegendcytidiseldddnightpred
81    
92    
103    
114    
125   35.00
136    
147    
158    
169    
1710    
1811    
1912    
2013    
2114   35.00
2215    
2316    
2417    
2518    
2619    
2720    
2821    
2922    
3023    
3124   25.00
3225    
3326    
3427    
3528    
3629    
3730    
381    
39TOTAL0.00 лв.0.00 лв.0.00 лв.95.00 лв.
IME SLUJITEL
Cell Formulas
RangeFormula
D8:D38D8=IF(SUMIFS(PREDSTAVITELNI!$H$6:$H$65,PREDSTAVITELNI!$B$6:$B$65,'IME SLUJITEL'!$D$7,PREDSTAVITELNI!$A$6:$A$65,'IME SLUJITEL'!$A8)=0,"",SUMIFS(PREDSTAVITELNI!$H$6:$H$65,PREDSTAVITELNI!$B$6:$B$65,'IME SLUJITEL'!$D$7,PREDSTAVITELNI!$A$6:$A$65,'IME SLUJITEL'!$A8))
E8:E38E8=IF($C8="Извънградско", 40, IF($C8="Градско", 20,""))
F8:F38F8=IF(SUMIFS(PREDSTAVITELNI!$H$6:$H$65,PREDSTAVITELNI!$B$6:$B$65,'IME SLUJITEL'!$F$7,PREDSTAVITELNI!$A$6:$A$65,'IME SLUJITEL'!$A8)=0,"",SUMIFS(PREDSTAVITELNI!$H$6:$H$65,PREDSTAVITELNI!$B$6:$B$65,'IME SLUJITEL'!$F$7,PREDSTAVITELNI!$A$6:$A$65,'IME SLUJITEL'!$A8))
G8:G38G8=IF(SUM(SUMIFS(PREDSTAVITELNI!$H$6:$H$65,PREDSTAVITELNI!$B$6:$B$65,{"GORIVO";"AVTOMOBILI";"POCHERPKI";"HRANA";"OBQD";"TALON PARKING";"AVTOMIVKA";"NOSHUVKI";"MEDICINSKI KONSUMATIVI";"KOMANDIROVYCHNI";"KANCELARSKI MATERIALI";"OFIS KONSUMATIVI";"OFIS TEHNIKA";"TEHNIKA";"STROITELNI MATERIALI";"OBUCHENIE";"PCR TEST";"PODARYK ROJDEN DEN";"PODARYK IMEN DEN"},PREDSTAVITELNI!$A$6:$A$65,'IME SLUJITEL'!$A8))=0,"",SUM(SUMIFS(PREDSTAVITELNI!$H$6:$H$65,PREDSTAVITELNI!$B$6:$B$65,{"GORIVO";"AVTOMOBILI";"POCHERPKI";"HRANA";"OBQD";"TALON PARKING";"AVTOMIVKA";"NOSHUVKI";"MEDICINSKI KONSUMATIVI";"KOMANDIROVYCHNI";"KANCELARSKI MATERIALI";"OFIS KONSUMATIVI";"OFIS TEHNIKA";"TEHNIKA";"STROITELNI MATERIALI";"OBUCHENIE";"PCR TEST";"PODARYK ROJDEN DEN";"PODARYK IMEN DEN"},PREDSTAVITELNI!$A$6:$A$65,'IME SLUJITEL'!$A8)))
D39:G39D39=SUM(D8:D38)
A8A8=DATE(godina,$B$4,1)
A9A9=DATE(godina,$B$4,2)
A10A10=DATE(godina,$B$4,3)
A11A11=DATE(godina,$B$4,4)
A12A12=DATE(godina,$B$4,5)
A13A13=DATE(godina,$B$4,6)
A14A14=DATE(godina,$B$4,7)
A15A15=DATE(godina,$B$4,8)
A16A16=DATE(godina,$B$4,9)
A17A17=DATE(godina,$B$4,10)
A18A18=DATE(godina,$B$4,11)
A19A19=DATE(godina,$B$4,12)
A20A20=DATE(godina,$B$4,13)
A21A21=DATE(godina,$B$4,14)
A22A22=DATE(godina,$B$4,15)
A23A23=DATE(godina,$B$4,16)
A24A24=DATE(godina,$B$4,17)
A25A25=DATE(godina,$B$4,18)
A26A26=DATE(godina,$B$4,19)
A27A27=DATE(godina,$B$4,20)
A28A28=DATE(godina,$B$4,21)
A29A29=DATE(godina,$B$4,22)
A30A30=DATE(godina,$B$4,23)
A31A31=DATE(godina,$B$4,24)
A32A32=DATE(godina,$B$4,25)
A33A33=DATE(godina,$B$4,26)
A34A34=DATE(godina,$B$4,27)
A35A35=DATE(godina,$B$4,28)
A36A36=DATE(godina,$B$4,29)
A37A37=DATE(godina,$B$4,30)
A38A38=DATE(godina,$B$4,31)
Named Ranges
NameRefers ToCells
godina='IME SLUJITEL'!$A$4A8:A38
Cells with Data Validation
CellAllowCriteria
F8:G38Any value
A4List=$T$2:$T$32
B4List=$U$2:$U$13
B8:B38List=$B$41:$B$45
C8:C38List=$C$56:$C$57
D8:D38Any value



TEST AUTO ADD.xlsm
ABCDEFGHIJKLMNOPQ
1otchetsivovoPRED
2на0АвтомобилиGORIVO
3за времето от 1.6.2021до30.6.2021ХранаAVTOMOBILI
4Офис консумативиPOCHERPKI
5datepredosnovaniepartniordokumentfakturadata na fakturataobshta stoinostfirmabulstatОфис техникаHRANA
624.6.2021POCHERPKI423423423424.6.202125.00 лв.DSVGSD312412412ЧетвъртъкМедицински консумативиOBQD
75.6.2021AVTOMIVKA3423425.6.202135.00 лв.DSFSFSDFSDG255445СъботаПочерпкиTALON PARKING
814.6.2021MEDICINSKI KONSUMATIVI323214.6.202110.00 лв.ПонеделникAVTOMIVKA
914.6.2021POCHERPKI3241414.6.202125.00 лв.ПонеделникNOSHUVKI
100.1.1900СъботаMEDICINSKI KONSUMATIVI
110.1.1900СъботаKOMANDIROVYCHNI
120.1.1900СъботаKANCELARSKI MATERIALI
130.1.1900СъботаOFIS KONSUMATIVI
140.1.1900СъботаOFIS TEHNIKA
150.1.1900СъботаTEHNIKA
160.1.1900СъботаSTROITELNI MATERIALI
170.1.1900СъботаOBUCHENIE
180.1.1900СъботаPCR TEST
190.1.1900СъботаPODARYK ROJDEN DEN
200.1.1900СъботаPODARYK IMEN DEN
210.1.1900Събота
220.1.1900Събота
230.1.1900Събота
240.1.1900Събота
250.1.1900Събота
260.1.1900Събота
270.1.1900Събота
280.1.1900Събота
290.1.1900Събота
300.1.1900Събота
310.1.1900Събота
320.1.1900Събота
330.1.1900Събота
340.1.1900Събота
350.1.1900Събота
360.1.1900Събота
370.1.1900Събота
380.1.1900Събота
390.1.1900Събота
400.1.1900Събота
410.1.1900Събота
420.1.1900Събота
430.1.1900Събота
440.1.1900Събота
450.1.1900Събота
460.1.1900Събота
470.1.1900Събота
480.1.1900Събота
490.1.1900Събота
500.1.1900Събота
510.1.1900Събота
520.1.1900Събота
530.1.1900Събота
540.1.1900Събота
550.1.1900Събота
560.1.1900Събота
570.1.1900Събота
580.1.1900Събота
590.1.1900Събота
600.1.1900Събота
610.1.1900Събота
620.1.1900Събота
630.1.1900Събота
640.1.1900Събота
650.1.1900Събота
66ОБЩО ЗА ПЕРИОДА:95.00 лв.
PREDSTAVITELNI
Cell Formulas
RangeFormula
D2D2='IME SLUJITEL'!C2
D3,G3D3='IME SLUJITEL'!D4
A6:A65A6=IFERROR(G6," ")
N6:N65N6=CHOOSE(WEEKDAY($G6),"Неделя","Понеделник","Вторник","Сряда","Четвъртък","Петък","Събота")
H66H66=SUM(H6:H65)
Cells with Data Validation
CellAllowCriteria
B6:B65List=Categoriq
C6:C65List=OFFSET(INDIRECT(SUBSTITUTE($B6;" ";""));0;0;COUNTA(INDIRECT(SUBSTITUTE($B6;" ";"")&"Col"));1)
D6:D65Any value
E6:E65List=OFFSET($S$2;0;0;COUNTA($S:$S);1)
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Do the following:
1. On the "PREDSTAVITELNI" sheet, create a named range, for example: data_PRED, for the range Q2:Q50.
2. On sheet "IME SLUJITEL", for cells D8 to G38 change the custom cell format to:
0;0;;@

Try these formulas
Dante Amor
ABCDEFG
7monlegendcytidiseldddnightpred
801/06/2021   85
IME SLUJITEL
Cell Formulas
RangeFormula
D8D8=SUMIFS(PREDSTAVITELNI!$H$6:$H$65,PREDSTAVITELNI!$B$6:$B$65,$D$7,PREDSTAVITELNI!$A$6:$A$65,$A8)
E8E8=IF($C8="Извънградско", 40, IF($C8="Градско", 20,""))
F8F8=SUMIFS(PREDSTAVITELNI!$H$6:$H$65,PREDSTAVITELNI!$B$6:$B$65,$F$7,PREDSTAVITELNI!$A$6:$A$65,$A8)
G8G8=SUMPRODUCT((PREDSTAVITELNI!$A$6:$A$65=A8)*(ISNUMBER(MATCH(PREDSTAVITELNI!$B$6:$B$65,data_PRED,0)))*(PREDSTAVITELNI!$H$6:$H$65))
Named Ranges
NameRefers ToCells
data_PRED=PREDSTAVITELNI!$Q$2:$Q$50G8
 
Upvote 0
Solution
Hello name
this is amazing, you won't even need macros.
Thank you very much for your quick cooperation.
I wish you good health!
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
advance formula needed for avg
Replies
4
Views
165
etaf
etaf
J
  • Solved
Calculate last 5 soccer team goals using data from another sheet on same workbook.
2
Replies
19
Views
865
felixstraube
F
A
  • Solved
Problem with multiple IFS, IF and AND in long formula
Replies
2
Views
116
arendberg
A
P
  • Question
Using Dynamic Validation Lists for every row in table
Replies
0
Views
135
psycoperl
P
R
  • Solved
Combination Sumif and/or Countif on a condition
Replies
3
Views
134
Fluff
Fluff

Forum statistics

Threads
1,214,387
Messages
6,119,208
Members
448,874
Latest member
Lancelots

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top