1

I'm stuck on copying a formula in Google sheets. I want to copy a formula to a cell every 25th row, but increase the reference by just one row.

✅ example, I'd like these formulas to go into L4, L29, and L54

=transpose(split(ArrayFormula(concatenate(SUPIR!$N3:$AB3&" "))," "))
=transpose(split(ArrayFormula(concatenate(SUPIR!$N4:$AB4&" "))," "))
=transpose(split(ArrayFormula(concatenate(SUPIR!$N5:$AB5&" "))," "))

❌ what I get is...

=transpose(split(ArrayFormula(concatenate(SUPIR!$N3:$AB3&" "))," "))
=transpose(split(ArrayFormula(concatenate(SUPIR!$N28:$AB28&" "))," "))
=transpose(split(ArrayFormula(concatenate(SUPIR!$N53:$AB53&" "))," "))

I just don't know how to change the references with this formula. Any help gratefully received.

The script I've been using to set other formulas is here, but I haven't been able to get it to run successfully...

function setFormulas() {

  var sheet, startRow, i; 
  sheet = SpreadsheetApp.getActiveSheet();
  startRow = 4;
  i = 4;
  while(startRow < 775) {
    sheet.getRange(startRow, 12).setFormula('=transpose(split(ArrayFormula(concatenate(SUPIR!N3:AB3&" "))," "))' + i);
    i++;
    startRow += 25;
  }
}
  • Are you doing this with Google Apps Script? if so what have you tried? – Robin Gertenbach Oct 10 '16 at 8:34
  • I did most of the ones I need to set 1000's of times via scripts, but I'm stuck on how to enter this formula in a script successfully. I'll edit the original question to include the script. – Matt Oct 10 '16 at 15:52
0

You are just appending i to the end of the formula. This will create the formulas as you want them

function setFormulas() {
  var sheet, startRow, i; 
  sheet = SpreadsheetApp.getActiveSheet();
  startRow = 4;
  i = 3;
  while(startRow < 775) {
    sheet.getRange(startRow, 12).setFormula('=transpose(split(ArrayFormula(concatenate(SUPIR!$N'+i+':$AB'+i+'&" "))," "))');
    i++;
    startRow += 25;
  }
}
| improve this answer | |
  • Robin, cheers, it works, and its now all but complete! A year ago I didn't know how to add two cells together or make a border. Had to start to learn as my business was so disorganized. A big thank you to you, and everyone here who helps people like me with no experience in excel / sheets.. okay that's enough ;) – Matt Oct 10 '16 at 20:27
  • You're welcome and best of luck :) – Robin Gertenbach Oct 10 '16 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.