32648

arrayformula that can “skip” rows

Question:

I need to introduce functionality into a google spreadsheet that will allow the user to edit the result of an array formula. The reason for the requirement is that an ARRAYFORMULA sets a default value for a group of cells, but the user sometimes needs to overwite these defaults. I'd like to know if this is even remotely possible.

example:

Row(#)|Array_1 |Array_2 ------------------------------------ 1 |a |=arrayformula(Array_1) 2 |b |"" 3 |c |"" 4 |d |""

So all rows in Array_2 are populated by an array formula. However the user wants to go directly to the second cell in Array_2 and change its value. Of course, by design ARRAYFORMULA will break. Is there some way to modify ARRAYFORMULA, so that it will simply skip over the cell that the user has edited and continue on its way as if nothing has happeded?

Answer1:

I realize this is an old problem but I was searching for this today and made a script that works for me.

This script puts a formula in an adjacent cell when a cell is edited in the second column. This way you can just overwrite the formula if you need to input something manually and you don't need to have the formulas go into all of the rows beforehand. I had people accidentally edit the formula and mess it up most of the time when they were pre-filled, so this works better for me.

function onEdit() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheetList = ["Sheet1","Sheet2","Sheet3"]; // list of sheets to run script on for (i = 0; i < sheetList.length; i++) { var sheetName = ss.getSheetByName(sheetList[i]); // only runs if sheet from sheetList is found if (sheetName != null) { var aCell = sheetName.getActiveCell(); var col = aCell.getColumn(); var adjacentCell = aCell.offset(0, -1); var formula = 'INPUT FORMULA HERE'; // put the formula you want in the adjacentCell here. Don't use it in an arrayformula // only runs if active cell is in column 2, if the adjacentCell is empty, and if the active cell is not empty(otherwise it runs if you delete something in column 2) if(col==2 && adjacentCell.getValue()=="" && aCell.getValue()!="") { adjacentCell1.setValue(formula); } } } }

Answer2:

Will changing the value not throw out the output of the remaining formulas?

If not, you could set up 2 new tabs: one which will receive the user over-ride values, and another "reflection" tab which you populate with

IF(tabOverride!Rx:Cy, tabOverride!Rx:Cy, tabArray!Rx:Cy)

basically the new tabs are cloned layouts of your array tab, creating an override input layer, plus a presentation layer that uses the IF('override value exists', 'then show override', 'else show array out put') logic to return the desired values.

hope that makes sense!

Recommend

  • JQuery - Change certain element classes to specific classes if certain other element is present
  • Shortest path in a grid
  • Java catching exceptions and subclases
  • Do stored procedures have the ability to delete a file from the OS?
  • How to mock current date?
  • Get count of created entries for each day
  • Convert a 12 hour time format to 24 hour time format (keeping record of the day) in python
  • send data back from jsp iterator to struts action class
  • Sending rails errors to rspec output
  • coldfusion variables into powershell
  • Allocating a 2D contiguous array within a function
  • How to Divide an array on c#?
  • Returning this from a constructor function in JS
  • Problem in concatenation of objects in javascript
  • Highcharts - Column chart with empty columns for date in x-axis
  • Send emails through VB6 if no email client
  • Double-click autofill - dynamic based on adjacent cell
  • Why cout is producing no output on Code Blocks?
  • git add error : “fatal : malloc, out of memory”
  • How to make JSON.NET deserialize to Microsoft Date Time?
  • android.support.v7.widget.Toolbar VectorDrawableCompat IllegalStateException when using support lib
  • Check for zero lines output from command over SSH
  • Silverlight DependencyProperty.SetCurrentValue Equivalent
  • Groovy: Unexpected token “:”
  • How to use carriage return with multiple line?
  • Replace value with Factor in r data.table
  • Refering to the class itself from within a class mehod in Objective C
  • Extracting HTML between tags
  • How to access EntityManager inside Entity class in EJB3
  • DomPDF {PAGE_NUM} not on first page
  • Repeat a vertical line on every page in Report Builder / SSRS
  • Javascript simulate pressing enter in input box
  • javaw.exe and eclipse startup problems
  • How to recover from a Spring Social ExpiredAuthorizationException
  • ILMerge & Keep Assembly Name
  • Importing jscolor library in angular 2
  • Large data - storage and query
  • WOWZA + RTMP + HTML5 Playback?
  • How do I rollback to a specific git commit
  • A cron job substitute?