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!

 

[whitfoaj]

Thank you very much! I'm down to what appear to be my last issues now.

Using this code (the same as yours, but modified names):

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

Everything seems to add up except that the two highlight portions both end up with a #REF error in calculation. I do have the workbook open. I've checked the extension and path. Do you know anything else that could cause it to throw a #REF for these two items?

Thanks very much!

 

[Andrew Poulsom]

If you have the workbook open why does your formula include the path?

 

[whitfoaj]

Excellent point...and things appear to be working properly now with a couple tweaks.

I do have one last question: my COUNTIF formula is actually referencing the output of another long formula (that is the 'Invoices Due'!A25 that appears in the last line of the formula). If I try to use the output of the formula (which comes out to 12SWC) I get no matches, but if I just directly input "12SWC" in say...Cell B25, then it works.

I know that these formulas are very picky about matches - is there something that I'm doing wrong that it won't count the formula result, or is this something I'll have to work around?

As a last note - thank you so much for your help! I've at least got a functioning formula now, and that would not have happened without your patience and knowledge!

 

[Andrew Poulsom]

What's the formula that returns 12SWC? Make sure that there aren't any leading or trailing spaces.

 

[whitfoaj]

This is the formula that I am using:

=IF(ISERR(INDEX('Aging Report'!F:F,MATCH('Calculations Sheet'!D24,'Aging Report'!H:H,0))),"",INDEX('Aging Report'!F:F,MATCH('Calculations Sheet'!D24,'Aging Report'!H:H,0)))

It's just pulling an invoice number from a prior sheet and then using that to pull the job number from the aging report.

Thanks!

 

[Andrew Poulsom]

There must be something different about what's in column F on Aging Report and the entries in the sheets in CPRAdjustments.xlsm. Maybe try:

=IF(ISERR(INDEX('Aging Report'!F:F,MATCH('Calculations Sheet'!D24,'Aging Report'!H:H,0))),"",TRIM(INDEX('Aging Report'!F:F,MATCH('Calculations Sheet'!D24,'Aging Report'!H:H,0))))

 

[whitfoaj]

That worked wonders. Thank you sir, for all your time and your help.

I think I'm going to close this one --- I might wait until the end of the day to do so in case I stumble upon another issue, but I think you've solved my dilemmas completely.

Much appreciated!