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?

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?

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?