Good afternoon,
I am working on a spreadsheet that will use information in a company's certified payroll reports to document if a vendor has submitted the required documentation through a certain date.
The CPR report will contain current data and this new workbook must search the entire CPR workbook (titled "CPRAdjustments.xlsm), made up of about 20 worksheets, each titled with a job symbol (ex: 10AAA).
This is to be done by referencing the job number on an invoice in the spreadsheet, searching the CPRAdjustments workbook for a worksheet that includes that job symbol in Cell A1, then conducting a VLookup for the vendor name on the invoice within only that worksheet and returning the value in a specified cell.
Because I can't post attachments, let me draw references:
Vendor number for my example: "CCCCC" --- located in Cell A5 of the 'Invoices Due' Worksheet in the "TEST" Workbook (the one I'm working on).
Job number for the invoice of the example: "10AAA" --- located in Cell A7 of the 'Invoices Due' Worksheet in the "TEST" workbook.
Cell A1 of the 'Invoices Due' worksheet contains the name of the CPR workbook: "CPRAdjustments.xlsm"
In the CPRAdjustments workbook:
First tab is the "INDEX" tab and contains a list of all worksheet names in the workbook from H20:H30.
Cell A1 of the "10AAA" worksheet contains --- "10AAA" for reference
Column A in the 10AAA worksheet contains vendor numbers (this is the case in all worksheets within the CPR workbook)
Column J in the 10AAA worksheet contains the date that CPR is covered through (as is the case in all worksheets in the CPR workbook)
This is the formula I am currently trying to get to work, but I am not ashamed to admit that I think I've stumbled in over my head. It is to be entered in the 'Invoices Due' worksheet.
=VLOOKUP('Invoices Due'!A98,INDIRECT("'"&INDEX('[CPRAdjustments.xlsm]INDEX'!$H$20:$H$30,MATCH(1,--(COUNTIF(INDIRECT("'["&A1&"]"&"'[CPRAdjustments.xlsm]INDEX'!$H$20:$H$30''!&$A$1"),'Invoices Due'!E98)>0),0))&"'!$A$4:$g$100"),7,FALSE)
Thank you in advance to anyone that can and will take the time to help me out. Know that I very much appreciate it!
Best,
AJ
I am working on a spreadsheet that will use information in a company's certified payroll reports to document if a vendor has submitted the required documentation through a certain date.
The CPR report will contain current data and this new workbook must search the entire CPR workbook (titled "CPRAdjustments.xlsm), made up of about 20 worksheets, each titled with a job symbol (ex: 10AAA).
This is to be done by referencing the job number on an invoice in the spreadsheet, searching the CPRAdjustments workbook for a worksheet that includes that job symbol in Cell A1, then conducting a VLookup for the vendor name on the invoice within only that worksheet and returning the value in a specified cell.
Because I can't post attachments, let me draw references:
Vendor number for my example: "CCCCC" --- located in Cell A5 of the 'Invoices Due' Worksheet in the "TEST" Workbook (the one I'm working on).
Job number for the invoice of the example: "10AAA" --- located in Cell A7 of the 'Invoices Due' Worksheet in the "TEST" workbook.
Cell A1 of the 'Invoices Due' worksheet contains the name of the CPR workbook: "CPRAdjustments.xlsm"
In the CPRAdjustments workbook:
First tab is the "INDEX" tab and contains a list of all worksheet names in the workbook from H20:H30.
Cell A1 of the "10AAA" worksheet contains --- "10AAA" for reference
Column A in the 10AAA worksheet contains vendor numbers (this is the case in all worksheets within the CPR workbook)
Column J in the 10AAA worksheet contains the date that CPR is covered through (as is the case in all worksheets in the CPR workbook)
This is the formula I am currently trying to get to work, but I am not ashamed to admit that I think I've stumbled in over my head. It is to be entered in the 'Invoices Due' worksheet.
=VLOOKUP('Invoices Due'!A98,INDIRECT("'"&INDEX('[CPRAdjustments.xlsm]INDEX'!$H$20:$H$30,MATCH(1,--(COUNTIF(INDIRECT("'["&A1&"]"&"'[CPRAdjustments.xlsm]INDEX'!$H$20:$H$30''!&$A$1"),'Invoices Due'!E98)>0),0))&"'!$A$4:$g$100"),7,FALSE)
Thank you in advance to anyone that can and will take the time to help me out. Know that I very much appreciate it!
Best,
AJ