automatic calculation issue

[firoz.raj]

I Simple Want .when user should go on total Column .automatically it needs to be calculated and needs to be come in Total . (Opening + receiving ).let me know the idea .Any help would be highly appreciated .(opening+receiving) calculate value needs to be come in total Cell .

 

[Brianwarnock]

You could write code for the worksheet_selectionchange event but it is simple enough just to drag the formula down.

Brian

 

[Brianwarnock]

Here is the code if you wish to do this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 5 Then
     Target.Value = Target.Offset(0, -2) + Target.Offset(0, -1)
End If
End Sub

Brian

 

[thingssocomplex]

Another way to do this if using Excel 2007 and that would be to format your data as a table when a user adds data to another row it would automatically add in the formula and do the calculations

 

[Brianwarnock]

I cannot comment on any features in 2007 as I don't have it.

firoz

You were on the right lines not sure what your full code was and why it did not work, so ran a simple test on your spreadsheet and this works

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 5 Then
    Target.Value = Target.Offset(0, -2) + Target.Offset(0, -1)
ElseIf Target.Column = 7 Then
    Target.Value = Target.Offset(0, -2) - Target.Offset(0, -1)
End If

End Sub

Brian

 

[firoz.raj]

Still Nooooooooot Working !!!. here is the following Code . What I have written !!! .Please see the Attachment especially see In the Delegate Chicken Brazil Worksheet_SelectionChange. and please let me know !!!.

[/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Comic Sans MS]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 5 Then
     Target.Value = Target.Offset(0, -2) + Target.Offset(0, -1)
ElseIf Target.Value = 7 Then
     Target.Value = Target.Offset(0, -2) - Target.Offset(0, -1)
End If
End Sub

 

[Brianwarnock]

ElseIf Target.Value = 7 Then

should be

ElseIf Target.Column = 7 Then

One's own syntax/typo errors can be hard to spot, but I'm astonished that atleast 1 of the others who have looked at this did not spot that.

Brian

 

[firoz.raj]

Hi, Brianwarnock
Now I simple want Closing needs to be Come Automatically in the Next Day in the opening coloumn . let me know the idea .How should I Achieve the specific Task ???.Kindly find the attachment .and let me know the idea .

 

[Brianwarnock]

I haven't time to completely automate this with code but you could use a Vlookup

In the current spreadsheet assume the next day starts in row 19
in the opening col C19 type
=VLOOKUP(B19,B$3:G$17,6,FALSE)

then if motherboard is typed in B19 the C19 will show 49666, type the next material type and drag the formula down by clicking on the bottom right corner of the cell.


Of course you could just copy and paste both columns if the content and order are constant

Brian