With this method, you’ll get partial automation, without having to add macros to the file.
Any time you close then re-open the file, the pivot table will be refreshed.
I indicated in my post #1 that I uploaded, on net, a sample of the Excel sheets referred to, and I provided the link in that post #1.
The upload is intended to provide any members wishing to help with a clearer picture of what my working sheets look like - so they can determine the best solution.
Meanwhile, I believe that using a find/replace approach will NOT be particularly helpful in this case. Kenny In my earlier post #1, I have considered that the idea of returning ‘Y’ or ‘YES’ in the Validation column might be a bit involving and difficult to achieve on the part of anyone who might be tempted to help.
Consequently, I will be OK with disregarding that part of my initial request, and therefore wish to restate my request as follows: I need help with either a 'VLOOKUP' or ‘VLOOKUP combined with Replace’; a ‘VB code’ or any ‘Excel formula’ that will replace the texts ‘UNPAID’ in whichever cells they appear in Sheet 2 Column G, with the actual dates paid, as shown in Sheet 1 Column B, in respect of all the invoice numbers listed also in Sheet 1 Column A.
It would be nice if Excel would automatically refresh a pivot table, whenever its source data changes, but unfortunately, there isn’t a built-in way to make that happen.
Here are 3 ways that you can refresh a pivot table, after you make changes to the source data If you add new records, or delete records, or edit the existing data, the pivot table doesn’t show the revised data right away.
Naming a range is relatively easy and when you use the name rather than the reference in a formula it really aids the understanding of the formula.There are instructions on my Contextures website, for copying VBA code to your own files.When sales details are entered and the invoice has been paid, I will enter all the details of the invoice (columns A to F) and in addition also enter the exact date the invoice was paid in the ‘Date Paid’ column G.Upon matching the invoice no, in the Data Table, it will update the relevant cell in the Data Table ‘Date Paid’ column G – replacing the text ‘UNPAID’ with the exact date paid.The reason for the ‘Y’ or ‘YES’ in the validation column is so that when the formula or VB code is activated to undertaking the matching and automatic updating, it will ignore all such rows with ‘Y’ or ‘YES’ in the Validation column and match and update only those rows without ‘Y’ or ‘YES’. If yes, I think a couple vlookup formulas settle the problem pretty easily.There isn’t any setting the you can change, to make Excel automatically refresh a pivot table.