Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to copy data from 1 workbook to another based on an invoice #
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to copy data from 1 workbook to another based on an invoice#
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 On Oct 23, 10:43*am, Patty C. <Patty wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to copy data from 1 workbook to another based on an invoi
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" wrote: 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 On Oct 23, 10:43 am, Patty C. <Patty wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to copy data from 1 workbook to another based on an invoi
I don't know. Do you have links to external data in the workbook
(EditLinks)? 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 On Oct 24, 2:47*pm, Patty C. wrote: 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" wrote: 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 On Oct 23, 10:43 am, Patty C. <Patty wrote: 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?- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy cells based on conditions to another workbook | Excel Discussion (Misc queries) | |||
Copy cells based on conditions to another workbook | Excel Worksheet Functions | |||
Copy cells based on conditions in one workbook to another workbook | Excel Discussion (Misc queries) | |||
Copy cells based on conditions in one workbook to another workbook | Excel Worksheet Functions | |||
help creating invoice in excel based on data and | Excel Discussion (Misc queries) |