Combining 2 columns into one using INDIRECT in Google Spreadsheet

1
votes

I have a Spreadsheet with multiple sheets. In one of them I'm trying to populate a column combining the value of two columns from another sheet.

Something like: Col1: City, Col2: Country -> In new Sheet, Location = (Col2), (Col1).

I've tried this but so far no luck:

=INDIRECT("FormaData!P2:P"&"FormData!O2:O")

FormData is the name of the external sheet, and the columns that I'm trying to automatically populate from are:

P2 to end of P (city) and O2 to end of O (country).

Any help will be highly appreciated!

1
excelgoogle-sheetsspreadsheet
Why not in your first cell =FormaData!P2 & ", " & FormaData!O2 and copy down? – Scott Craner
I did this =INDIRECT(FormData!P2&", "&FormData!O2), and it gives me error: Function INDIRECT parameter 1 value is 'Barcelona, España'. It is not a valid cell/range reference. – Eric Mitjans
Don't use the indirect. Just the formula straight – Scott Craner

1 Answers

3
votes

This should work all the way down

=arrayformula('FormaData'!P2:P&", "&'FormaData'!O2:O)

Are you using a different spreadsheet altogether? If so you need to modify it to use import range:

=ARRAYFORMULA(IMPORTRANGE("spreadsheet url","'FormaData'!P2:P")&","&(IMPORTRANGE("spreadsheet url","'FormaData'!O2:O")))