[excel] Auto populate columns in one sheet from another sheet

I would like to populate columns in sheet2 from sheet1. If I have column A in Sheet1 I want A in Sheet2 to have the same information.

I tried using =sheet1!A1 but it only returns the value from A1 in sheet1. I tried using =sheet1!A but it only returns #NAME?.

If Column A from Sheet1 has a dynamic range (it can be empty or have 500 or 1000 rows (I'm populating sheet1 from my database)). How do I use some of those columns in another sheet showing all 500 or 1000 rows?

In Google Sheets you can use =ArrayFormula(Sheet1!B2:B)on the first cell and it will populate all column contents not sure if that will work in excel

If I understood you right you want to have sheet1!A1 in sheet2!A1, sheet1!A2 in sheet2!A2,...right?

It might not be the best way but you may type the following

=IF(sheet1!A1<>"",sheet1!A1,"")

and drag it down to the maximum number of rows you expect.

Use the 'EntireColumn' property, that's what it is there for. C# snippet, but should give you a good indication of how to do this:

string rangeQuery = "A1:A1";

Range range = workSheet.get_Range(rangeQuery, Type.Missing);

range = range.EntireColumn;

I have used in Google Sheets

 ={sheetname!columnnamefrom:columnnameto}

Example:

1. ={sheet1!A:A}
2. ={sheet2!A4:A20}