Help on a formula to search multiple worksheets in another workbook using 2 criteria

whitfoaj

New Member
Joined
Mar 29, 2013
Messages
32
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

whitfoaj

New Member
Joined
Mar 29, 2013
Messages
32
Can anyone help me on this? I've been playing around with the formula and, to be honest, I'm at a loss here.

If anyone needs any additional details or clarification, please don't hesitate to ask.

Any and all help would be very much appreciated!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
Can anyone help me on this? I've been playing around with the formula and, to be honest, I'm at a loss here.

If anyone needs any additional details or clarification, please don't hesitate to ask.

Any and all help would be very much appreciated!

What is the expected outcome - a text value or number? Note that INDIRECT requires the wb to be open...
 

whitfoaj

New Member
Joined
Mar 29, 2013
Messages
32
What is the expected outcome - a text value or number? Note that INDIRECT requires the wb to be open...

Hello,
The expected outcome is the date that the certified payroll was last updated. MM/DD/YY by default.

And I tried to do enough reading to be at least somewhat competent when working on this - having the referenced workbook open for INDIRECT functions was one of the most common problems I saw, so I've made sure that's not it.

I might have 99 problems, but having the book closed isn't one. :)

Thanks very much for your time and any help!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,344
Messages
5,635,751
Members
416,877
Latest member
SolarTheory

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top