Google-sheets – Find-Replace with discontinous ranges

google sheetsgoogle-sheets-named-rangessearch

I am editing a Google Sheet with multiple peers. I need to do a Find-Replace on just my rows, say, rows 1:38 and 197:222. (Not permitted to sort the data.) I see many examples on the web of doing this over multiple sheets, ex:

 Sheet1!K37:K68;Sheet2!K37:K68

I figured for my data the following would work:

 Sheet1!K37:K38;Sheet1!K197:K208

No, "Invalid Range". Next tried:

 Sheet1!K37:K38;K197:K208

No joy again. I went back to the web and did Google searches and SE searches but could not find an example that matches my requirements. This should be really easy. What am I missing?

Best Answer

If you give a sample sheet and more specific examples I can accommodate this answer towards a more specific example.

You have permission to sort...

Step 1) Copy the sheet

Step 2) Create an extra column with the original row order

Step 3) Sort the sheet by your entries

Step 4) Find and replace your changes which are now congruous

Step 5) Sort back with the column you made in step 2

Step 6) Copy all your changes back into the original sheet in step 1

Of course getting permission to resort and having the original row order column in the original sheet is a better/common sense solution.

Related Solutions

Google Sheets – Using IF Statement with Ranges

The problem is that you are passing the IF function an array (range), while it only supports a single value.

Try

=IF(MIN(PAGE!C6:D11) < 0, "Some words", "Some other words")

The MIN function finds the minimum value in your range.

Google Sheets – Maximum Number of Named Ranges

I was able to make 4963 named ranges, before the execution time of the Google Apps Script expired, using this piece of code.

Code

function namedRanges() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  for(var i=1; i<5000; i++) {
    var r = ss.getRange('A'+i);
    ss.setNamedRange("test"+i, r);
  }
}

Screenshot

enter image description here

If you plan on making larger named ranges (I created only one celled ranges), I guess it will take longer and thus reduce the amounts you can make.

If you're about to add them manually, I'd say you're safe.