Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I copy a row depending on the content of a cell in the row
I have a report that has in column A codes for different facilities. I would
like to create worksheet in the workbook that copy the contents of the rows from the master worksheet depending on the contents of the cell in Column A. For example A B C D SFMC 17 22 44 MAMC 12 13 37 SFMC 19 30 70 From this report on worsheet 1 would like to have a formula in worksheet 2 that will copy the contents of any row in worksheet 1 that has SFMC in column A. I have tried the IF statement but I can only get it to copy the contents of column A not the entire row. thanks for any advice! Bob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I copy a row depending on the content of a cell in the row
Assuming your data are in Sheet1!A2:D4. Target code in Sheet2!F1.
Let us say you start your tabulation in Sheet2!A2. Then, in this cell (*array* formula - commit with Shift+Ctrl+Enter): =IF(COUNTIF(Sheet1!$A$2:$A$4,$F$1)=ROWS($A$2:A2), SMALL(IF(Sheet1!$A $2:$A$4=$F$1,ROW(Sheet1!$A$2:$A$4)-ROW(Sheet1!$A$2)+1),ROWS($A $2:A2)),"") This is an auxiliary column, you can hide it if necessary. In B2: =IF(A2="","",INDEX(Sheet1!$A$2:$D$4,$A2,COLUMNS($B $1:B1))) Copy the B2 formula down and across to suit. HTH Kostis Vezerides On Oct 19, 7:39 pm, Robert59 wrote: I have a report that has in column A codes for different facilities. I would like to create worksheet in the workbook that copy the contents of the rows from the master worksheet depending on the contents of the cell in Column A. For example A B C D SFMC 17 22 44 MAMC 12 13 37 SFMC 19 30 70 From this report on worsheet 1 would like to have a formula in worksheet 2 that will copy the contents of any row in worksheet 1 that has SFMC in column A. I have tried the IF statement but I can only get it to copy the contents of column A not the entire row. thanks for any advice! Bob |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I copy a row depending on the content of a cell in the row
ColA, ColB, ColC, and ColD are defined name ranges
In Sheet 2 B2: =IF(ISERR(SMALL(IF(ColA=$A$2,ROW(INDIRECT("1:"&ROW S(ColA)))),ROWS($1:1))),"",INDEX(ColB,SMALL(IF(Col A=$A$2,ROW(INDIRECT("1:"&ROWS(ColA)))),ROWS($1:1)) )) ctrl+shift+enter, not just enter copy across from B2 to D2 In C2: change ColB to ColC (cse) In D2: change ColB to ColD (cse) select B2:D2 and copy down as far as needed "Robert59" wrote: I have a report that has in column A codes for different facilities. I would like to create worksheet in the workbook that copy the contents of the rows from the master worksheet depending on the contents of the cell in Column A. For example A B C D SFMC 17 22 44 MAMC 12 13 37 SFMC 19 30 70 From this report on worsheet 1 would like to have a formula in worksheet 2 that will copy the contents of any row in worksheet 1 that has SFMC in column A. I have tried the IF statement but I can only get it to copy the contents of column A not the entire row. thanks for any advice! Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum data, depending on cell content | Excel Worksheet Functions | |||
copy comment content to cell content as data not as comment | Excel Discussion (Misc queries) | |||
how do i change cell background colour depending on its content? | Excel Discussion (Misc queries) | |||
Copy content of cell to another depending on value of third cell(between worksheets) | Excel Worksheet Functions | |||
changing the colour of cells depending on the content. | Excel Discussion (Misc queries) |