0

I have one string that is dynamically built with existing ranges across multiple sheets. All these ranges are exactly the same structurally but with different data. I want to join them all into one master table for further processing. One example of such a string:

{04.06.2016!B8:H;18.06.2016!B8:H;09.07.2016!B8:H;15.07.2016!B8:H;24.07.2016!B8:H;30.07.2016!B8:H;06.08.2016!B8:H}

The path above is a result of the following formula:

=CONCATENATE("{";JOIN(";";FILTER(S3:S;NOT(ISBLANK(S3:S))));"}")

I want to use the result of this formula with some function like INDIRECT. The problem is that INDIRECT does not support arrays.

Is that possible?

  • 1
    I don't think that appending edits is a good practice. If you think that question was not clear edit the question thinking on people that will read the question for first time. The revision history is just a click away. – Rubén Aug 18 '16 at 13:25
  • I think that you should use Google Apps Script instead of built-in functions. Please add more details like how may sheets are required and if that number if fixed or dynamic. – Rubén Aug 18 '16 at 13:44
  • Thank you Rubén for alerting about the edits. As for the Google Script, I think I'll create my own function. That may be the solution. – Naftali Aug 18 '16 at 14:02
2

I managed to solve the problem by writing the script below. One change I did is that the value no longer contains "{}" characters at the start/end of the string.

function TEST_INDIRECTS() {
  INDIRECTS("04.06.2016!B8:H;18.06.2016!B8:H;09.07.2016!B8:H;15.07.2016!B8:H;24.07.2016!B8:H;30.07.2016!B8:H;06.08.2016!B8:H");
}

function INDIRECTS(string_array) {
  ret = [];

  var sheet = SpreadsheetApp.getActiveSheet();

  items = string_array.split(";");

  for (var idx in items) {
    var item = items[idx];
    var range = sheet.getRange(item);
    var range_array = range.getValues();

    MERGE_ARRAYS(ret, range_array);
  }

  return ret;
}

function MERGE_ARRAYS(a, b) {
  for (var idx in b) {
    var item = b[idx];

    if (!IS_EMPTY_ARRAY(item)) {
      a.push(item);
    }
  }
}

function IS_EMPTY_ARRAY(item) {
  for (idx in item) {
    if(item[idx] != "") {
      return false;
    }
  }

  return true;
}
| improve this answer | |

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.