Copy a column dependant on a the contents of a cell
 

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

Copy a column dependant on a the contents of a cell
 

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" wrote in message
...
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

Copy a column dependant on a the contents of a cell
 

Thanks very much Biff, works a treat but I cannot follow why it works,
Many thanks
Peter
"T. Valko" wrote in message
...
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" wrote in message
...
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

Copy a column dependant on a the contents of a cell
 

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" wrote in message
...
Thanks very much Biff, works a treat but I cannot follow why it works,
Many thanks
Peter
"T. Valko" wrote in message
...
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" wrote in message
...
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

Copy a column dependant on a the contents of a cell
 

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


"T. Valko" wrote in message
...
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" wrote in message
...
Thanks very much Biff, works a treat but I cannot follow why it works,
Many thanks
Peter
"T. Valko" wrote in message
...
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" wrote in message
...
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

Copy a column dependant on a the contents of a cell
 

You're welcome. Thanks for the feedback!

Biff

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


"T. Valko" wrote in message
...
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" wrote in message
...
Thanks very much Biff, works a treat but I cannot follow why it works,
Many thanks
Peter
"T. Valko" wrote in message
...
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" wrote in message
...
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