Excel Automate Changing Formula on Corresponding Tabs.

Elia, Nicholas (HEALTH) 20 Reputation points
2024-10-01T12:32:23.99+00:00

I am trying to use the Automate feature to simplify some data structuring for my team. Once properly arranged said data is dispersed across two tabs in the same file using a Filtering formula. Once I engage the Automate function the formula on the corresponding pages is altered. However, if I make the changes manually, the formula is not changed. Examples below.

Formula before Automate is used and if the changes are made manually.

=FILTER(nys_nh_facility_incident_report!$A$3:$L$100,nys_nh_facility_incident_report!$F$3:$F$100=$A$1,"EMPTY")

Formula from tab 2 &3 after using Automate.

=FILTER(nys_nh_facility_incident_report!$A$3:$M$67,nys_nh_facility_incident_report!$H$3:$H$67=$A$1,"EMPTY")

The Automate script is below

unction main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Insert at range E3:F100 on selectedSheet, move existing cells right
	selectedSheet.getRange("E3:F100").insert(ExcelScript.InsertShiftDirection.right);
	// Delete range I3:I100 on selectedSheet
	selectedSheet.getRange("I3:I100").delete(ExcelScript.DeleteShiftDirection.left);
	// Text to columns on range D3:D100 on selectedSheet
	for (let row = 0; row < selectedSheet.getRange("D3:D100").getRowCount() ; row++) {
		let sourceRange = selectedSheet.getRange("D3:D100");
		let destinationRange = selectedSheet.getRange("D3");
		let sourceRangeValues = sourceRange.getRow(row).getValues()[0][0].toString().split(/[ ]/)
		destinationRange.getOffsetRange(row, 0).getResizedRange(0, sourceRangeValues.length - 1).setValues([sourceRangeValues]);
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,887 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,896 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,715 questions
0 comments No comments
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.