Help With Indirect Match Syntax

[whitfoaj]

Hello,

I am currently working on a formula that will search an external workbook (while open, as it is Indirect) for a specific worksheet and then use VLOOKUP to return the value of a cell on that page.

The detailed explanation: I work for a construction company and this formula will look at a list of existing invoices and match the vendors to the date their certified payroll has been submitted through (in the external workbook). In our Certified Payroll (CPR) workbook, we have each job broken out in a separate worksheet, named using its job number (ex: 11AAA or 13NCE).

This formula *should* look at an invoice, then match the job number to a worksheet in the external workbook using a named range on the first worksheet in the CPR workbook. Once it matches the number to a specific sheet, it *should* use VLOOKUP to check the vendor name (column A) and return the date their CPR covers through (column F).

This is the code I have so far:

=VLOOKUP('Invoices Due'!A2,
INDIRECT("'["&A1&".xlsm]&INDEX([CPRAdjustments.xlsm]SheetList,MATCH(1,--(COUNTIF(INDIRECT("'["&A1&".xlsm]&SheetList&!$A$4:$F$70"),'Invoices Due'!A2)>0),0))&'!$A$4:$F$70),2,FALSE)

The problem I currently have (and might have more after I fix it - I'm not sure) is the red highlighted text. When I try to enter this as a function, I get the "The formula you typed contains an error" message and it highlights the red text. I'm not sure why.

Can anyone help me get this up and running? I'm really learning a lot as I go, but this function has been a wall for almost three weeks now.

Any help very much appreciated!

Thank you!

 

[Andrew Poulsom]

Is it?

=VLOOKUP('Invoices Due'!A2,
INDIRECT("'["&A1&".xlsm]"&INDEX([CPRAdjustments.xlsm]SheetList,MATCH(1,--(COUNTIF(INDIRECT("'["&A1&".xlsm]"&SheetList&"'!$A$4:$F$70"),'Invoices Due'!A2)>0),0))&"'!$A$4:$F$70"),2,FALSE)

 

[whitfoaj]

Andrew: that solved that issue in no time. Thank you very much for your help.

If I could be so bold as to ask another favor - I now get new errors.

=VLOOKUP('Invoices Due'!A2,
INDIRECT("'["&A1&".xlsm]"&INDEX('C:\Users\awhitford\Desktop\CPRAdjustments.xlsm'!JobList,MATCH(1,--(COUNTIF(INDIRECT("'["&A1&".xlsm]"&JobList&"'!$A$4:$F$70"),'Invoices Due'!A2)>0),0))&"'!$A$4:$F$70"),2,FALSE)


In this case, the red highlighted piece comes up with a #REF error (this is the full file path to the external book). I'm guessing that I'm trying to reference the named range incorrectly, but I'm not entirely sure what I'm doing that is incorrect.

The second error, highlighted in blue, comes up with #NAME for just that piece - again, I'm sure I'm making some foolish, simple mistake that I'll kick myself for after hearing how it's done.

Thanks for all your help so far - any chance you have a little more time to waste on a learning Excel user?

EDIT: You may notice that "SheetList" is now "JobList" - this is the actual name of the Named Range, and I should have changed it in the original post but didn't notice in time. Thanks!

 

[Andrew Poulsom]

Is there a range named JobList in CPRAdjustments.xlsm?

 

[whitfoaj]

There is. It is currently located on the first worksheet, titled "JOBS" and is in cells H20:H30.


-- removed inline image ---

 

[Andrew Poulsom]

So there are two ranges named JobList - one in CPRAdjustments and one in the workbook named in A1 (which must be open)? What do those names refer to?

 

[whitfoaj]

Well, cell A1 actually just contained the text "CPRAdjustments" --- I was copying straight from the Microsoft support page showing how to link to external workbooks using Indirect. They both refer to the same workbook, same list.

The JobList range is actually just the worksheet names of the CPRAdjustments workbook. It includes ten job numbers from H20 to H30.

Now that I guess I don't need the cell links, I guess this is what the formula effectively shows anyway:

=VLOOKUP('Invoices Due'!A2,
INDIRECT("'[CPRAdjustments.xlsm]"&INDEX('C:\Users\awhitford\Desktop\CPRAdjustments.xlsm'!JobList,MATCH(1,--(COUNTIF(INDIRECT("'[CPRAdjustments.xlsm]"&JobList&"'!$A$4:$F$70"),'Invoices Due'!A2)>0),0))&"'!$A$4:$F$70"),2,FALSE)

Red and Blue highlighted issues are the same.

Thanks for all your time and effort - I'd still be stuck at part one without it!

 

[Andrew Poulsom]

What would your formula look like without using INDIRECT? I don't follow this part:

"'[CPRAdjustments.xlsm]"&JobList&"'!$A$4:$F$70"

Is JobList the name of a worksheet?

 

[whitfoaj]

I don't know how to get the formula to search an entire workbook for a specific worksheet title, then search that worksheet, without using Indirect. I used Google to search for someone doing something (remotely) similar to what I'm doing and tried to modify their formula accordingly. The big issue is that they weren't using an external workbook.

I have not seen a rule that forbids outside links, but I know that is custom on some forums - if that is the case here, please let me know and I will delete the link to the article I used immediately.

Excel VLOOKUP Multiple Sheets

And to answer your other question: there is no worksheet named "JobList" - only the named range. I was trying to solve the problem myself and saw multiple statements that a named range can only exist in one place in a workbook, therefore a worksheet name is unnecessary. Please correct me if I'm wrong.

Thank you!

 

[Andrew Poulsom]

Using the example in the link you posted this works for me:

=VLOOKUP(A2,INDIRECT("'[CPRAdjustments.xlsm]"&INDEX(CPRAdjustments.xlsm!SheetList,MATCH(1,--(COUNTIF(INDIRECT("'[CPRAdjustments.xlsm]"&CPRAdjustments.xlsm!SheetList&"'!$A$1:$C$4"),A2)>0),0))&"'!$A$1:$C$4"),2,FALSE)

Don't forget Ctrl+Shift+Enter.