Real Time updating a Column in a table based on a Column in another Table

[sangeet230]

Hi there!

Maybe there is an easier solution to this because I am over thinking.

I have a small business and I make all my invoices through excel. I maintain a Customer Master in Table format in one sheet where I enter each new customer and assign a unique code. Next, I enter all the sales data in another sheet. I want to extend the functionality and make a Customer Balance Tracker in the same workbook. What I simply want is to have a sheet where i have all the Customer Codes(unique ones), Total Bill Value, Total receipt and Balance Amount.

I want that as soon as I enter a new customer in the Customer Master Sheet, the new tracker sheet be updated immediately with that new customer code. The Total Bill Value and Receipts can be SUMIFS from other sheets. How do I automatically update the Customer Code column in the Balance Tracker sheet with a new customer inserted in the Customer Master Sheet?

I understand that I can simply create a link. Example- Say suppose I entered 10 new customers in a day and I forgot to extend my other Table in the Balance Tracker sheet. I don't want to miss out a customer from the tracker.

Any help, appreciated! Thanks!

 

[mrshl9898]

You could have the Unique List as a Pivot of the master.

Then add this code to the Master Sheet, change A:A to whatever column the customer name is in. As you add or modify data it will refresh the pivot.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range

Set KeyCells = Range("A:A")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

ThisWorkbook.RefreshAll

End If
End Sub

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[sangeet230]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

This is my first post on the forum. I'll update them right away.

 

[sangeet230]

You could have the Unique List as a Pivot of the master.

Then add this code to the Master Sheet, change A:A to whatever column the customer name is in. As you add or modify data it will refresh the pivot.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range

Set KeyCells = Range("A:A")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

ThisWorkbook.RefreshAll

End If
End Sub

I think this might do. Thanks a ton. I'll try it.

 

[Peter_SSs]

I'll update them right away.

Thanks for updating your details.
Unfortunately the suggestion that I had in mind would require Excel 365. Hopefully though you are on the way to a satisfactory result anyway.