In Google Spreadsheets how do I put in a formula in after a transpose? [closed]

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?

closed as unclear what you're asking by pnuts, serenesat, ale, jonsca♦Sep 22 '17 at 1:28

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

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() {

}

// 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)
}

// 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

• 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