Copy a column dependant on a the contents of a cell

[Peter]

I wish to copy a column from one of several worksheets to a summary sheet
dependant upon the month.
EG. If cell A1 in the summary sheet contained January I would wish to copy
column c from the January sheet to column b of the summary sheet.
I really could do with some suggestions on how to achieve this.

Thanks in advance
Peter

 

[T. Valko]

One way:

=IF(A$1="","",IF(INDEX(INDIRECT(A$1&"!C:C"),ROWS($1:1))="","",INDEX(INDIRECT(A$1&"!C:C"),ROWS($1:1))))

Copy down as needed.

Biff

 

[Peter]

Thanks very much Biff, works a treat but I cannot follow why it works,
Many thanks
Peter

 

[T. Valko]

Most of the formula is taken up by testing for empty/blank cells:

=IF(A$1="","",IF(INDEX(INDIRECT(A$1&"!C:C"),ROWS($1:1))="","",

If A1 (cell holding month name) was empty that would cause the formula to
error. If the target cells on the target sheet are empty then the formula
would return 0's. I assumed you didn't want either of those situations to
occur.

INDEX(INDIRECT(A$1&"!C:C"),ROWS($1:1)))

As you drag copy down, the ROWS($1:1) argument will increment like this:

INDEX(INDIRECT(A$1&"!C:C"),ROWS($1:1))) = C1
INDEX(INDIRECT(A$1&"!C:C"),ROWS($1:2))) = C2
INDEX(INDIRECT(A$1&"!C:C"),ROWS($1:3))) = C3
INDEX(INDIRECT(A$1&"!C:C"),ROWS($1:4))) = C4
etc
etc

Biff

 

[Peter]

Many thanks Biff, that helps me to understand what is going on and it makes
a bit more sense now.
Regards
Peter

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Many thanks Biff, that helps me to understand what is going on and it
makes a bit more sense now.
Regards
Peter