Script error

Question

I have a script saved in a saved workbook template. Once a data set is pasted into the new workbook template, I run the Script, but get back an #N/A error. Columns referenced are formatted identically (text) and cell the Script populates is formatted as General, but Text is not working either. The Script uses an INDEX/MATCH formula that references another workbook. When I type in the INDEX/MATCH formula manually, it works, but the Script returns the #N/A error. Any ideas?

Answer 1

Hi @Heimann, Justin,

Thank you for posting your question in the Microsoft Q&A forum.

Based on your sharing, the behavior that the manual formula works but the Script doesn't indicates that the issue is related to the way the script handles the external reference. There are two possible reasons for this situation:

• External workbook issue: When a VBA Macro or an Office Script uses a formula referencing an external workbook, the formula usually needs the source workbook to be open at the moment.
• Formula syntax in the script

If possible, can you share us the script code you're using and also the formula that works when adding manually so I can take a look to clearly understand the situation and provide the most appropriate solution for your concern.

Please understand that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.

Thank you for your understanding and cooperation. I'm looking forward to your reply.

Answer 2

Hi @Heimann, Justin,

Thank you for your update.  

With your sharing, you are using Office Scripts and unfortunately, Office Scripts cannot reference external workbooks. Therefore, even though your formula works manually in Excel, the Script cannot proceed because it cannot resolve the external reference to the file '[Z.MANUAL-BOM QTYS.xlsx]LOGIC ID'. 

Additionally, you are right about the next path that moving to Power Automate as the solution. The idea is to use Power Automate to access both workbooks and transfer the data to allow the Office Script to work locally.

Since it's related to Power Automate, I recommend posting your scenario in the Microsoft Power Platform Community Forum Thread. This forum is dedicated to Power Automate and is actively monitored by experienced users, Microsoft partners, and engineers who can provide detailed guidance and share best practices.   

Apologies for redirecting you to a different community as we're forum moderators, our primary role is to guide users toward the appropriate resources and support channels and the category you initially posted in primarily focuses on Microsoft 365-related topics, therefore we may not have the depth of expertise needed for Power Automate-specific questions.  

Thank you for your patience and understanding throughout this process. Should you have any questions or need further assistance, feel free to reach out at any time.   

I look forward to hearing from you.

Answer 3

Formula: =INDEX('[Z.MANUAL-BOM QTYS.xlsx]LOGIC ID'!$A:$B,MATCH(D2,'[Z.MANUAL-BOM QTYS.xlsx]LOGIC ID'!$B:$B,0),1)

It's a formula that references a database of information shared on a company shared drive.

The script is just a recording of typing that formula in manually and naming.

I'm reading about saving all workbooks in OneDrive and using PowerAutomate Flow for Iteration to run the script across multiple sources. Seems to be the way to go.

Script:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range E2 on selectedSheet
	selectedSheet.getRange("E2").setFormulaLocal("=INDEX('[Z.MANUAL-BOM QTYS.xlsx]LOGIC ID'!C1:C2,MATCH(D2,'[Z.MANUAL-BOM QTYS.xlsx]LOGIC ID'!C2,0),1)");
}