| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct issues | Excel Worksheet Functions | |||
| How do I combine spreadsheets and documents in one file? | Excel Discussion (Misc queries) | |||
| macro | Excel Discussion (Misc queries) | |||
| Conditional Format as a MACRO | Excel Worksheet Functions | |||
| Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Linear Mode
