Updating data table automatically from another table

[kenny9002]

I am not sure how to do this. Could members of the forum please help me.


<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>I have a Data Table from which I create a Pivot Table. The Data Table is housed in a Worksheet named DATA TABLE – and the table has the following columns & their headings:
<o></o>
A = Invoice No.
B = Invoice Date
C = Month (in mmm-yy format)
D = Gross Amount
E = Vat Amount
F = Net Amount
G = Date Paid<o></o>
<o></o>
This Data Table currently contains very many rows of sales entries made. And more are added regularly as sales are made.
<o></o>
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. However, if the said invoice is not paid, I will type in the text ‘UNPAID’ against the invoice in the same ‘Date Paid’ column G. In other words, the column G contains dates paid for paid invoices) in the format e.g. 01/05/2011. The same column contains the text ‘UNPAID’ for those invoices that yet unpaid.
<o></o>
Because there are too many rows in the Data Table, whenever we receive payment notification, we have difficulty going through the Data Table in order to match the Invoice Nos. on the payment notification with the Invoice Nos. in the Data Table and then remove the text ‘UNPAID’ and replace it with the actual date of payment.
<o></o>
I have created another Worksheet named RECEIPTS with the following columns and headings:
<o></o>
A = Invoice No.
B = Date Paid
C = Validation
<o></o>
What I intend to achieve is this: Whenever I receive a payment notification, instead of going through the very many rows of entries in the Data Table and looking for each invoice number and then taking off the text ‘UNPAID’ and typing in the date paid, I will list all the Invoice Nos and the Date Paid from the payment notification onto my second worksheet - the Receipts worksheet.
<o></o>
I need help on how I can have the Receipts worksheet automatically update the Data Table, using Excel formula or VB code.
<o></o>
a) The Excel formula or VB code will pick up each Invoice No. and its related payment date from the Receipts Worksheet – column A and B, respectively, then examine each of the cells in the Data Table ‘Invoice Nos.’ column A. 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.

b) For each row in the Receipts Worksheet completed, it will insert the text ‘Y’ or ‘YES’ against the row in the Validation column.

c) 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’.
<o></o>
I have uploaded a small sample of copies of the two worksheets referred to. The link is as follows:

http://www.box.net/shared/ztslhdnzde60if1miy0u
<o></o>
Thanks all for your anticipated kind help.
<o></o>
Kenny
<o></o>

 

[sirguise]

Are all invoices always paid in full? If yes, I think a couple vlookup formulas settle the problem pretty easily. If not, you may have to get into some vb code to make it work correctly as your "Date Paid" column might conceivably have more than one value.

Another simple solution is to change that value of "Unpaid" to InvoiceNumber + "Unpaid", then you could do a find/replace on InvoiceNumber + "Unpaid".

 

[kenny9002]

Yes, all invoices are always paid in full. I hope that our Excel experts in this forum would, please, look into this for me and kindly suggest a solution using either vlookup, Excel formula or VBA code - whichever that will help me to achieve the desired result pretty easily.

I indicated in my post #1 that I uploaded, on www.box.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.

I appreciate any help, please.

Kenny

 

[kenny9002]

Help please, anyone - Or is more information needed?

Thanks.

Kenny

 

[kenny9002]

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:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have the following two sheets:
<o></o>
a) Sheet 1 named (Receipts) with 2 columns as follows:

Column A = Invoice No.
Column B = Date Paid (current receipts)
<o></o>
b) Sheet 2 named (DataTable) with about 8 columns which include the following:
<o></o>
Column A = Invoice No.
Column G = Date Paid
<o></o>
The invoice numbers in Sheet 1 column A are also available in Sheet 2 column A.
<o></o>
Column G in Sheet 2 contains both the actual dates invoices were paid (in dd/mm/yyyy format) and also the text ‘UNPAID’ for those invoices not yet paid.
<o></o>
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.
<o></o>
As indicated above, I will be OK with this, and should be grateful if anyone can, please, help.
<o></o>
I suppose the data that I uploaded in the link below may still be useful to anyone willing to help.
<o></o>
http://www.box.net/shared/ztslhdnzde60if1miy0u
<o></o>
Please help.

Thanks all.
<o></o>
Kenny