Make the worksheet dynamic

kvemperor

New Member
Joined
Jan 13, 2023
Messages
31
Office Version
  1. 2021
Platform
  1. Windows
sample.jpg

In this worksheet i have the function in cell G9 =B9+G8-E9 so every time i am putting a value in the column B or in the column E then when i drag down the cell (of the same row)in the column G then i have my result.Is it possible this to make it dynamic?(with vba or any other way? i mean instead of drugging down the cell of the column G down when i am putting a value in the column B or in the column E the result comes automatically in the column G.
I wouldn't mind when i was putting the date in the column F then automatically comes the result in the column G
thank you in advance
 
ok forget post#27 it just depends on column F . keep in your mind if the cells are not filled in column G then doesn't show any thing in column I
I think this will fix all of the problems.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Or Target.Row < 8 Then Exit Sub
Application.EnableEvents = False
If Target.Column = 3 And Target.Row > 8 Or Target.Column = 6 And Target.Row > 8 Then
Dim LR1 As Integer
LR1 = Range("G" & Rows.Count).End(xlUp).Row
For s = 9 To LR1
Range("I9:I" & s).FormulaR1C1 = "=RC[-6]+R[-1]C-RC[-3]"
Next
End If
Application.EnableEvents = True
End Sub
 
Upvote 0

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
ok forget post#27 it just depends on column F . keep in your mind if the cells are not filled in column G then doesn't show any thing in column I
I think this will fix all of the problems.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Or Target.Row < 8 Then Exit Sub
Application.EnableEvents = False
If Target.Column = 3 And Target.Row > 8 Or Target.Column = 6 And Target.Row > 8 Then
Dim LR1 As Integer
LR1 = Range("G" & Rows.Count).End(xlUp).Row
For s = 9 To LR1
Range("I9:I" & s).FormulaR1C1 = "=RC[-6]+R[-1]C-RC[-3]"
Next
End If
Application.EnableEvents = True
End Sub
ΚΑΡΤΕΛΛΑ ΔΕΙΓΜΑ.xlsx
ABCDEFGHI
1 ONOMA
2
3
4
5
6
7ΗΜΕΡΑΑΡ.ΣΥΝΝΑΛΑΓΗΣ ΤΙΜ ΑΞΙΑ ΤΙΜ ΑΡ.ΤΙΜΑΡ.ΣΥΝΝΑΛΑΓΗΣ ENANTI ΑΞΙΑ ENANTI ΗΜΕΡΟΜΗΝΙΑ ΠΙΣΤΩΣΗ/ΕΝΑΝΤΙ ΝΕΟ ΥΠΟΛΟΙΠΟ
8 
9 - €
10Εκ μεταφορας2022 - €
11Δευτέρα12.000,00 €TIM 5 06/2/2023ΠΙΣΤΩΣΗ2.000,00 €
12Τετάρτη25.000,00 €TIM 6 01/3/2023ΠΙΣΤΩΣΗ7.000,00 €
13Πέμπτη 1500,00 €02/3/2023ΕΝΑΝΤΙ6.500,00 €
14Παρασκευή3700,00 €TIM 803/3/2023ΠΙΣΤΩΣΗ7.200,00 €
15Σάββατο 2250,00 €04/3/2023ΕΝΑΝΤΙ6.950,00 €
16Παρασκευή4800,00 €TIM 9 10/3/2023ΠΙΣΤΩΣΗ7.750,00 €
17Παρασκευή5100,00 €TIM 10 03/3/2023ΠΙΣΤΩΣΗ
18   
19   
ΕΤΑΙΡΙΑ NEO TΕΛΕΥΤΑΙΟ (3)
Cell Formulas
RangeFormula
A11:A400A11=LET(x,G11:G400,IF(x="","",WEEKDAY(x)))
E11:E13,E15:E19E11=IF(F11="","",COUNTA($F$11:F11))
I9:I16I9=C9+I8-F9
B11:B19B11=IF(D11="","",COUNTA($D$11:D11))
H8:H19H8=IF(AND(C8="",F8=""),"",IF(C8<>"",$L$2,$L$3))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H8:H399Expression=H8=$L$3textNO
H8:H399Expression=H8=$L$2textNO


Hallo my friend.Thank you very much again for your effort.This time is better but i still have to drug the cell I downwards 😔 . I always put value in column G.
 
Upvote 0
have to drug the cell I downwards 😔
I suspect that .

I always put value in column G.
I know that
you should put the date in column G firstly then you can add in column C or F , it will autofill the formula until last row contains value in column G.
at least just check the code !
I bet you fill column C or F firstly and column G secondly , then will not autofill the formula in column I .;)
 
Upvote 0
I suspect that .


I know that
you should put the date in column G firstly then you can add in column C or F , it will autofill the formula until last row contains value in column G.
at least just check the code !
I bet you fill column C or F firstly and column G secondly , then will not autofill the formula in column I .;)
Υou are right.if i put first the date in the column G then i always have the column Ι dynamic:biggrin:
Do you think you can put the column G (with the date) in column B🥺 (and after the columns C,D,E and F moved to the right).So the date will be at the beggining and i always put first the date.
This one will change and the vba code .If its a lot of effort of you then is ok.
you have spend a lot of time helping me and i really appreciate it
🥺
 
Upvote 0
Do you think you can put the column G (with the date) in column B🥺 (and after the columns C,D,E and F moved to the right).So the date will be at the beggining and i always put first the date.
This one will change and the vba code .If its a lot of effort of you then is ok.
I try to understand what you say but I don't understand you sorry !
just add pictures and explain every step to try do that to understand you .
you have spend a lot of time helping me and i really appreciate it
don't worry !
I'm beginner like you .if I can help you I will do it ,if not I will say you I can't help you .;)

just post the whole requirements without any adjusting every time until to find help from the rest of others members.
 
Upvote 0
I try to understand what you say but I don't understand you sorry !
just add pictures and explain every step to try do that to understand you .

don't worry !
I'm beginner like you .if I can help you I will do it ,if not I will say you I can't help you .;)

just post the whole requirements without any adjusting every time until to find help from the rest of others members.
You are in a higher level than me believe.I am learning programming in general now.

this is what i would like to be my worksheet
ΚΑΡΤΕΛΛΑ ΔΕΙΓΜΑ.xlsx
ABCDEFGHI
1 ONOMA
2
3
4
5
6
7ΗΜΕΡΑΗΜΕΡΟΜΗΝΙΑΑΡ.ΣΥΝΝΑΛΑΓΗΣ ΤΙΜ ΑΞΙΑ ΤΙΜ ΑΡ.ΤΙΜΑΡ.ΣΥΝΝΑΛΑΓΗΣ ENANTI ΑΞΙΑ ENANTI ΠΙΣΤΩΣΗ/ΕΝΑΝΤΙ ΝΕΟ ΥΠΟΛΟΙΠΟ
8 
9 
102022Εκ μεταφορας - €
11    - €
12   
13   
14  
15   
ΕΤΑΙΡΙΑ NEO TΕΛΕΥΤΑΙΟ (3)
Cell Formulas
RangeFormula
A11:A400A11=LET(x,B11:B400,IF(x="","",WEEKDAY(x)))
I10:I11I10=D10+I9-G10
F11:F13,F15F11=IF(G11="","",COUNTA($G$11:G11))
C11:C15C11=IF(E11="","",COUNTA($E$11:E11))
H8:H15H8=IF(AND(D8="",G8=""),"",IF(D8<>"",$L$2,$L$3))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H8:H399Expression=H8=$L$3textNO
H8:H399Expression=H8=$L$2textNO


I have drag the column G (wIth the date )in the column B
 
Upvote 0
and which you columns entering ?
I didn t enter any column.i only have dragged the column G and i have put it in the column B.The columns C,D,E and F went one position to the right.I believe the code maybe need to change.I didn t tried to check if we need to alter and the vba code 🥺

i have tried now.the code needs to change because it doesn t work :cry:
 
Upvote 0
Come on !!
just I want if you want enter values in columns C ,F as in earlier
 
Upvote 0
Come on !!
just I want if you want enter values in columns C ,F as in earlier
Ι never put values in column C.The column C fills automatically (with number order 1,2,3 etc) when i put a value in the column E and also i never put values in the column F.The column F fills automatically (with number order 1,2,3 etc) when i put a value in the column G.Its like i keep an order when i am putting the values in the columns E or G
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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