Auto-fill rows from columns in another sheet?

I'm using Google Doc's Spreadsheet at the moment and I'm attempting to create the same view in another sheet.

First Sheet:

enter image description here

(yes I need these merged cells)

Second Sheet:

enter image description here

Unfortunately Google Spreadsheet doesn't seem to recognise the pattern:

=Days!B1
=Days!D1
=Days!F1

Any ideas how I can fix this so I don't have to manually change the formula everytime I want to add a row?

Joshua Barnett

Posted 2014-09-14T18:58:56.007

Reputation: 505

So, you're working in a Google Spreadsheet, yet you add three Excel tags. Why don't you visit Web Applications? That's the place to ask all Google Apps related questions. – Jacob Jan Tuinstra – 2014-09-21T17:54:41.957

Answers

Starting this formula in row 2, it will retrieve every second cell value in row 1 of the "Day" sheet.

=INDEX(Days!$1:$1,(ROW()*2)-2)

Copy down.

enter image description here

teylyn

Posted 2014-09-14T18:58:56.007

Reputation: 19 551

Try this in the second sheet.

Formula

=TRANSPOSE(FILTER(Days!B1:1, Days!B1:1 <> ""))

Explained

The FILTER formula shows the range (Days!B1:1) by filtering out empty rows. The TRANSPOSE formula will re-arrange the appeance from a horizontal to vertical one.

This approach is meant to be used in Google Spreadsheets, taking full advantage of its functions.

Screenshot

enter image description here

Example

I've created an example file for you: Auto-fill rows from columns in another sheet?

Jacob Jan Tuinstra

Posted 2014-09-14T18:58:56.007

Reputation: 353