Vlookup code problem
I have three worksheets in a workbook
Sheet 1 a master dataset for unpaid invoices. Sheet 2 represents an updated datafile run 1 week later and sheet three represents an updated file run in week 2. I want to be able to update the master sheet with updated values on sheet two in week 1 and then again in week 2. (basically there is an invoice days in the dataset that represents how many days past due the payment is. I am using Vlookup to replace the updated # of days value, however, if there is no match it means the invoice has been paid, thus instead of #N/A I want the field to remain unchanged. (I have a different column using match formula that will add a cleared status and strike out the item.) I don't want to lose the record even though it has been paid as i then have a macro to remove all cleared items and archive them. Is it possible to use Vlookup or some other function within my macro to leave a field alone if it doesnt match the invoice number? |
Vlookup code problem
Hi
Use If and ISNA function together to fix your problem "Belinda7237" wrote: I have three worksheets in a workbook Sheet 1 a master dataset for unpaid invoices. Sheet 2 represents an updated datafile run 1 week later and sheet three represents an updated file run in week 2. I want to be able to update the master sheet with updated values on sheet two in week 1 and then again in week 2. (basically there is an invoice days in the dataset that represents how many days past due the payment is. I am using Vlookup to replace the updated # of days value, however, if there is no match it means the invoice has been paid, thus instead of #N/A I want the field to remain unchanged. (I have a different column using match formula that will add a cleared status and strike out the item.) I don't want to lose the record even though it has been paid as i then have a macro to remove all cleared items and archive them. Is it possible to use Vlookup or some other function within my macro to leave a field alone if it doesnt match the invoice number? |
Vlookup code problem
so my current formula is:
=VLOOKUP(D81,'Week 2 Dataset'!G:O,9,0) how would i use the if NA? Thanks! "singh" wrote: Hi Use If and ISNA function together to fix your problem "Belinda7237" wrote: I have three worksheets in a workbook Sheet 1 a master dataset for unpaid invoices. Sheet 2 represents an updated datafile run 1 week later and sheet three represents an updated file run in week 2. I want to be able to update the master sheet with updated values on sheet two in week 1 and then again in week 2. (basically there is an invoice days in the dataset that represents how many days past due the payment is. I am using Vlookup to replace the updated # of days value, however, if there is no match it means the invoice has been paid, thus instead of #N/A I want the field to remain unchanged. (I have a different column using match formula that will add a cleared status and strike out the item.) I don't want to lose the record even though it has been paid as i then have a macro to remove all cleared items and archive them. Is it possible to use Vlookup or some other function within my macro to leave a field alone if it doesnt match the invoice number? |
All times are GMT +1. The time now is 12:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com