Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

In my spreadsheet the transpose formula fills in a deliberately variable number of column cells, how do I fill in a cell after a transpose formula? I've created a youtube video to demonstrate (click the little cog for HD).

http://youtu.be/V6FtLPCaTyw

share|improve this question
add comment

1 Answer

There are three ways to resolve this phenomenon

First
Use an explicit range in your formula:
=TRANSPOSE(sheet2!A1:A12)

Second
Filter out empty cells:
=ARRAYFORMULA(TRANSPOSE(FILTER(Sheet2!A2:A, Sheet2Sheet2!A2:A<>"")))

Third
Remove all "physical" rows beyond A12 in sheet 2. Then your original formula will work as well

Afterwards, use this script to add additional information

// get active spreadsheet (GLOBAL)
var ss = SpreadsheetApp.getActive();

function onOpen() {
  // create menu
  var menu = [{name: "add additional data via script", 
                 functionName: "addData"},
    {name: "add additional data via formula", 
                 functionName: "addDataFormula"}];

  // add to menu
  ss.addMenu("Add Info", menu); 
}

function addData() {
  // get active sheet
  var sh = ss.getActiveSheet();

  // array with header names
  var aHeader = ss.getSheetByName("Extra").getDataRange().getValues();

  // get last filled column header and add one
  var emptyCol = sh.getLastColumn()+1;  

  // get range and set values
  var range = sh.getRange(1, parseInt(emptyCol), 1, aHeader.length)
    .setValues([aHeader]);
}

function addDataFormula() {
  // get active sheet
  var sh = ss.getActiveSheet();

  // array with header names
  var aHeader = ss.getSheetByName("Extra").getDataRange().getValues();

  // get last filled column header and add one
  var emptyCol = sh.getLastColumn()+1;  

  // get range and set values
  var formula = '=ARRAYFORMULA(TRANSPOSE(FILTER(Extra!A:A,Extra!A:A<>"")))';
  var range = sh.getRange(1, parseInt(emptyCol), 1, aHeader.length)
    .setFormula(formula );
}

The script adds an item to the menu structure called "Add Info". Choosing one of the items, will result in the addition of the extra information.
Add the script like this: tools, script editor. Make sure to de-bug (press bug) the code.

See example file I've prepared: example file

share|improve this answer
    
thanks Jacob, so after the transpose formula has filled out the columns, where do I put any subsequent formulas for columns I'd like adjacent to the transpose column? Hope that makes sense. –  conspirisi Mar 2 '13 at 14:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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