lookup refrence

[jitendras]

There is an example in below if i remove faulty value like (5) then it will add in warehouse (0) into (5)


hope i will get soon it great help to me thanks in adavence
Surat
Warehouse Stock Goods Faulty
0 5

I want like is below

Surat
Warehouse Stock Goods Faulty
5 0

[JohnTopley]

Don't understand: how do you "remove" it?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[jitendras]

Value as i mention like 5 and same value auotometic add in warehour stock

[JohnTopley]

How will we (Excel) know you want to take it out of "Faulty" and put into the Warehouse???

[jitendras]

Simply if we have value in faulty device like 5 and we would like to remove 5 from faulty it will get in warehouse sum and faulty get nil and ware house bucket will get fill like 5

[jitendras]

find example sheet

[JohnTopley]

so .. whenever a value is added to "Faulty" (column J) you want it immediately added to the "Faulty Warehouse" stock (Column H) (column J) and the "Faulty" set to 0

You will have to use VBA ("Worksheet Event") to initiate the required action AND you need to ADD the "faulty" to the existing "Faulty Warehouse" stock which cannot be done with a formula in "Warehouse Stock".

To do the VBA we need an exact copy of your real workbook if it is different to last post: are the respective columns H and J?

See attached: add value in J and H is updated.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Row = 1 Or Target.Column <> 10 Then Exit Sub

  On Error GoTo ErrHandler
  Application.EnableEvents = False
  
       Target.Offset(0, -2).Value = Target.Offset(0, -2).Value + Target.Value
       Target.Value = 0
ErrHandler:
  Application.EnableEvents = True
End Sub

[jitendras]

Thanks a lot it's work

But i have different situation

If i have faulty value 15 in this 15 quantity i have received 5 quantity then 5 add in warehouse stock remain stay at his place like (15-5) remain value (10)


same should be work which i added some more column in demo sheet Like "L","N","P","R","T","V"

If any way to get it

thanks in adavance

[JohnTopley]

I don't understand:

If i have faulty value 15 in this 15 quantity i have received 5 quantity then 5 add in warehouse stock remain stay at his place like (15-5) remain value (10)

Faulty
If you mean you have 15 in "Faulty" (in column J) and you want to move 5 to "Faulty Stock Warehouse" leaving 10 in J: cannot be done!

You will need a "Faulty Stock Transfer" column which would have 5 in it: this number would be added to "Fault Warehouse" stock and deducted from "Faulty".

[jitendras]

can you help on the same ???? as according you

[JohnTopley]

The attached has an example. I will not do any more changes until you post a final version of your workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Row = 1 Or Target.Column <> 11 Then Exit Sub

  On Error GoTo ErrHandler
  Application.EnableEvents = False
  
       Target.Offset(0, -3).Value = Target.Offset(0, -3).Value + Target.Value
       Target.Offset(0, -1).Value = Target.Offset(0, -1).Value - Target.Value
       Target.Value = 0
ErrHandler:
  Application.EnableEvents = True
End Sub

[jitendras]

Thanks

It's ok

as it is do work same which i added column same which you have done

[JohnTopley]

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Row <= 2 Then Exit Sub
  
   c = Target.Column
   r = Target.Row
   
   If Cells(2, c) <> "Faulty Transfer" Then Exit Sub ' Check this if this "Faulty Transfer": EXIT if not

  On Error GoTo ErrHandler
  Application.EnableEvents = False
  
       Cells(r, 8) = Cells(r, 8) + Target.Value  'Update "Faulty Warehouse" stock
       Target.Offset(0, -1).Value = Target.Offset(0, -1).Value - Target.Value  'Reduce "Faulty" stock
       Target.Value = 0  ' Reset "Faulty" to zero
       
ErrHandler:
  Application.EnableEvents = True
End Sub

[jitendras]

Thanks a lot it's been working

it's been great for me

[JohnTopley]

Glad it is all working.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.