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!
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!