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

sangeet230

New Member
Joined
Aug 2, 2016
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,723
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.

VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 2, 2016
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Aug 2, 2016
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
I'll update them right away.
Thanks for updating your details. (y)
Unfortunately the suggestion that I had in mind would require Excel 365. Hopefully though you are on the way to a satisfactory result anyway. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,974
Messages
5,627,949
Members
416,283
Latest member
casea14

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
Top