Need to copy data from 1 workbook to another based on an invoice #

[Patty C.]

I have 2 files which contain: 1) GL records with GL Code & Invoice #; 2)
Accessorial records with an Accessorial description and invoice #. I need
the GL code assigned for each Invoice # copied into the Accessorial file to
the same invoice #.

Can Excel do this?

 

[JP]

You can use INDEX/MATCH to look up the invoice number from the
Accessorial file in the GL code file, and return the associated
invoice number. Open both files and place this formula in the
Accessorial file next to the first invoice #:

=INDEX(<GL Code column in GL code workbook>,MATCH(<Invoice #>,<Invoice
# column in GL code workbook>,0))

Replace <> with the appropriate cell references.

For example:

GL code workbook has GL code and Invoice # in cells A2:B500,
Acessorial workbook has descriptions and Invoice # in cells D2:E50.
The formula would look like:

=INDEX('GL Workbook Name'!A2:A500,MATCH(E2,B2:B500,0))


Write back if you need further assistance.


HTH,
JP

 

[Patty C.]

JP-
When I hit enter after putting the formula in a newly created colunn in the
file, I get a "Update Values: GLs' box, what is it asking me to do?

 

[JP]

I don't know. Do you have links to external data in the workbook
(Edit>Links)? They might be trying to update themselves. Otherwise I
have no idea. It's just a lookup formula, it shouldn't trigger any
dialog boxes.

--JP