|
How to populate a summary page ????????????????????????????????????????
Dear All:
I have a excel workbook which contains two worksheets. Sheet 1 is my input page. Sheet 2 is my summary page. When my BINARY input numbers in any row under column A which is under sheet 1 are equal to 1, I would like the whole row to be copied to sheet 2. When my input numbers in any row under column A which is under sheet 1 are equal to 0, the whole row will NOT be copied to sheet 2. However, I don't like any gaps in sheet 2 AND I would like to keep the same order in sheet 2 as in sheet 1. In addition, I would like this to be done in an automatic procedure. No autofilter or any other manual efforts. The following is an illustration of what I would like to see: Under sheet 1 Column A ColumnB Column C ...... row1 1 Text1 1 row2 0 Text2 2 row3 0 Text3 3 row4 1 Text4 4 Since there are inputs in cell A1 and A2, the whole row 1 and row 2 will be copied AUTOMATICALLY to sheet 2 in the same order WITHOUT any gap as follows: Under sheet 2 ColumnA Column B Column C row1 1 Text1 1 row2 1 Text4 4 Anybody knows how to achieve this? It is my headache. I really appreciate your help! Thank you so much and I am looking forward to hearing from you soon! George |
How to populate a summary page ???????????????????????????????????
|
How to populate a summary page ????????????????????????????????????????
Hi George,
Explained he http://office.microsoft.com/en-us/as...260381033.aspx You'll be lookig up 1 in Column A and reurn multiple rows -- Kind regards, Niek Otten Microsoft MVP - Excel "George" wrote in message oups.com... | Dear All: | | I have a excel workbook which contains two worksheets. Sheet 1 is my | input page. Sheet 2 is my summary page. | | | When my BINARY input numbers in any row under column A which is under | sheet 1 are equal to 1, I would like the whole row to be copied to | sheet 2. | When my input numbers in any row under column A which is under sheet 1 | are equal to 0, the whole row will NOT be copied to sheet 2. | | | However, I don't like any gaps in sheet 2 AND I would like to keep the | same order in sheet 2 as in sheet 1. In addition, I would like this to | be done in an automatic procedure. No autofilter or any other manual | efforts. The following is an illustration of what I would like to see: | | | Under sheet 1 | | | Column A ColumnB Column C ...... | | | row1 1 Text1 1 | row2 0 Text2 2 | row3 0 Text3 3 | row4 1 Text4 4 | | | Since there are inputs in cell A1 and A2, the whole row 1 and row 2 | will be copied AUTOMATICALLY to sheet 2 in the same order WITHOUT any | gap as follows: | | | Under sheet 2 | | | ColumnA Column B Column C | row1 1 Text1 1 | row2 1 Text4 4 | | | Anybody knows how to achieve this? It is my headache. I really | appreciate your help! Thank you so much and I am looking forward to | hearing from you soon! | | | George | |
How to populate a summary page ????????????????????????????????????????
Hi George
If I were you I would have filtered the rows that contain 1 and copy the filtered range and paste it to the second sheet. The follovin code might do the job: Sub Test dim rngCur as range ' Current range Sheets("Sheet 1").activate ' assuming that you have data starting from A1 Range("A1").AutoFilter Field:=1, Criteria1:="1", Operator:=xlAnd Set rngCur= range("A1").CurrentRegion rngCur.Copy Sheets("Sheet 2").activate range("A1").Select Selection.PasteSpecial Paste:= xlPasteAll end sub Ömer Ayzan "George" wrote in message oups.com... Dear All: I have a excel workbook which contains two worksheets. Sheet 1 is my input page. Sheet 2 is my summary page. When my BINARY input numbers in any row under column A which is under sheet 1 are equal to 1, I would like the whole row to be copied to sheet 2. When my input numbers in any row under column A which is under sheet 1 are equal to 0, the whole row will NOT be copied to sheet 2. However, I don't like any gaps in sheet 2 AND I would like to keep the same order in sheet 2 as in sheet 1. In addition, I would like this to be done in an automatic procedure. No autofilter or any other manual efforts. The following is an illustration of what I would like to see: Under sheet 1 Column A ColumnB Column C ...... row1 1 Text1 1 row2 0 Text2 2 row3 0 Text3 3 row4 1 Text4 4 Since there are inputs in cell A1 and A2, the whole row 1 and row 2 will be copied AUTOMATICALLY to sheet 2 in the same order WITHOUT any gap as follows: Under sheet 2 ColumnA Column B Column C row1 1 Text1 1 row2 1 Text4 4 Anybody knows how to achieve this? It is my headache. I really appreciate your help! Thank you so much and I am looking forward to hearing from you soon! George |
How to populate a summary page ????????????????????????????????????????
Hi, Omer:
Thanks for your help! What I need is an automatic process. I do not like to do it manually. The only thing I would like to see is whenever I input 0 or 1 under column A on sheet 1, all rows with input 1 will go to sheet 2 IN THE SAME ORDER. I would appricate your efforts if you have an alternative to achieve this. George Ömer Ayzan wrote: Hi George If I were you I would have filtered the rows that contain 1 and copy the filtered range and paste it to the second sheet. The follovin code might do the job: Sub Test dim rngCur as range ' Current range Sheets("Sheet 1").activate ' assuming that you have data starting from A1 Range("A1").AutoFilter Field:=1, Criteria1:="1", Operator:=xlAnd Set rngCur= range("A1").CurrentRegion rngCur.Copy Sheets("Sheet 2").activate range("A1").Select Selection.PasteSpecial Paste:= xlPasteAll end sub Ömer Ayzan "George" wrote in message oups.com... Dear All: I have a excel workbook which contains two worksheets. Sheet 1 is my input page. Sheet 2 is my summary page. When my BINARY input numbers in any row under column A which is under sheet 1 are equal to 1, I would like the whole row to be copied to sheet 2. When my input numbers in any row under column A which is under sheet 1 are equal to 0, the whole row will NOT be copied to sheet 2. However, I don't like any gaps in sheet 2 AND I would like to keep the same order in sheet 2 as in sheet 1. In addition, I would like this to be done in an automatic procedure. No autofilter or any other manual efforts. The following is an illustration of what I would like to see: Under sheet 1 Column A ColumnB Column C ...... row1 1 Text1 1 row2 0 Text2 2 row3 0 Text3 3 row4 1 Text4 4 Since there are inputs in cell A1 and A2, the whole row 1 and row 2 will be copied AUTOMATICALLY to sheet 2 in the same order WITHOUT any gap as follows: Under sheet 2 ColumnA Column B Column C row1 1 Text1 1 row2 1 Text4 4 Anybody knows how to achieve this? It is my headache. I really appreciate your help! Thank you so much and I am looking forward to hearing from you soon! George |
How to populate a summary page ????????????????????????????????????????
Hi George,
<I apologize to give you a lot of troubles Don't! I'll try again! I started with a blank workbook (except for the input table on Sheet1) and followed my own recipe and it worked. Select A1:C4 on Sheet1. Define a name for this range, like LookupTab. Defining a name can be done via the InsertNameDefine menu or by clicking in the Name box, that is the box to the left of the Formula bar. Select the following formula and copy it: =IF(ISERROR(SMALL(IF(LookupTab=1,ROW(LookupTab)),R OW(1:1))),"",INDEX(LookupTab,SMALL(IF(LookupTab=1, ROW(LookupTab)),ROW(1:1)),COLUMN(A1))) Select A1 on Sheet2. Click in the formula bar and paste (do not press ENTER yet), then press-and-hold CTRL and SHIFT, press ENTER (you have now 3 keys pressed) and release the 3 keys. The formula should now show between curly brackets in the formula bar. Now you can copy down as many rows as you like, and then copy right to columns B and C Later on you can change the definition of LookupTab to accommodate your input rows. -- Kind regards, Niek Otten Microsoft MVP - Excel "George" wrote in message ps.com... | Hi, Niek: | | I am sure it is a precise work. I am so sorry that I did not get it | yet. Could you please show me the whole process in more detail | including which keys to use? I apologize to give you a lot of troubles. | | Thanks, | | George | | | |
How to populate a summary page ????????????????????????????????????????
<my own recipe
That's unfair. It is mostly Ashish Mathur's recipe -- Kind regards, Niek Otten Microsoft MVP - Excel "Niek Otten" wrote in message ... | Hi George, | | <I apologize to give you a lot of troubles | | Don't! | | I'll try again! I started with a blank workbook (except for the input table on Sheet1) and followed my own recipe and it worked. | | Select A1:C4 on Sheet1. | Define a name for this range, like LookupTab. Defining a name can be done via the InsertNameDefine menu or by clicking in the | Name box, that is the box to the left of the Formula bar. | Select the following formula and copy it: | | =IF(ISERROR(SMALL(IF(LookupTab=1,ROW(LookupTab)),R OW(1:1))),"",INDEX(LookupTab,SMALL(IF(LookupTab=1, ROW(LookupTab)),ROW(1:1)),COLUMN(A1))) | | Select A1 on Sheet2. | Click in the formula bar and paste (do not press ENTER yet), then press-and-hold CTRL and SHIFT, press ENTER (you have now 3 keys | pressed) and release the 3 keys. The formula should now show between curly brackets in the formula bar. | | Now you can copy down as many rows as you like, and then copy right to columns B and C | | Later on you can change the definition of LookupTab to accommodate your input rows. | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "George" wrote in message ps.com... || Hi, Niek: || || I am sure it is a precise work. I am so sorry that I did not get it || yet. Could you please show me the whole process in more detail || including which keys to use? I apologize to give you a lot of troubles. || || Thanks, || || George || || || | | |
How to populate a summary page ????????????????????????????????????????
Hi, Niek:
It is perfect. I do not know how many thanks I need to tell you. But, I will buy you a meal whenever you stop by Houston. E-mail me anytime and I will get in touch with you! Again, thank you so much for your help! George Niek Otten wrote: <my own recipe That's unfair. It is mostly Ashish Mathur's recipe -- Kind regards, Niek Otten Microsoft MVP - Excel "Niek Otten" wrote in message ... | Hi George, | | <I apologize to give you a lot of troubles | | Don't! | | I'll try again! I started with a blank workbook (except for the input table on Sheet1) and followed my own recipe and it worked. | | Select A1:C4 on Sheet1. | Define a name for this range, like LookupTab. Defining a name can be done via the InsertNameDefine menu or by clicking in the | Name box, that is the box to the left of the Formula bar. | Select the following formula and copy it: | | =IF(ISERROR(SMALL(IF(LookupTab=1,ROW(LookupTab)),R OW(1:1))),"",INDEX(LookupTab,SMALL(IF(LookupTab=1, ROW(LookupTab)),ROW(1:1)),COLUMN(A1))) | | Select A1 on Sheet2. | Click in the formula bar and paste (do not press ENTER yet), then press-and-hold CTRL and SHIFT, press ENTER (you have now 3 keys | pressed) and release the 3 keys. The formula should now show between curly brackets in the formula bar. | | Now you can copy down as many rows as you like, and then copy right to columns B and C | | Later on you can change the definition of LookupTab to accommodate your input rows. | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "George" wrote in message ps.com... || Hi, Niek: || || I am sure it is a precise work. I am so sorry that I did not get it || yet. Could you please show me the whole process in more detail || including which keys to use? I apologize to give you a lot of troubles. || || Thanks, || || George || || || | | |
How to populate a summary page ????????????????????????????????????????
Glad you got it working, George.
I had read Ashish's recipe before but this was the first time I applied it. So it was a useful exercise for me too! -- Kind regards, Niek Otten Microsoft MVP - Excel "George" wrote in message oups.com... | Hi, Niek: | | It is perfect. I do not know how many thanks I need to tell you. But, I | will buy you a meal whenever you stop by Houston. E-mail me anytime and | I will get in touch with you! | | Again, thank you so much for your help! | | George | | Niek Otten wrote: | <my own recipe | | That's unfair. It is mostly Ashish Mathur's recipe | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "Niek Otten" wrote in message ... | | Hi George, | | | | <I apologize to give you a lot of troubles | | | | Don't! | | | | I'll try again! I started with a blank workbook (except for the input table on Sheet1) and followed my own recipe and it worked. | | | | Select A1:C4 on Sheet1. | | Define a name for this range, like LookupTab. Defining a name can be done via the InsertNameDefine menu or by clicking in the | | Name box, that is the box to the left of the Formula bar. | | Select the following formula and copy it: | | | | | =IF(ISERROR(SMALL(IF(LookupTab=1,ROW(LookupTab)),R OW(1:1))),"",INDEX(LookupTab,SMALL(IF(LookupTab=1, ROW(LookupTab)),ROW(1:1)),COLUMN(A1))) | | | | Select A1 on Sheet2. | | Click in the formula bar and paste (do not press ENTER yet), then press-and-hold CTRL and SHIFT, press ENTER (you have now 3 | keys | | pressed) and release the 3 keys. The formula should now show between curly brackets in the formula bar. | | | | Now you can copy down as many rows as you like, and then copy right to columns B and C | | | | Later on you can change the definition of LookupTab to accommodate your input rows. | | -- | | Kind regards, | | | | Niek Otten | | Microsoft MVP - Excel | | | | "George" wrote in message ps.com... | || Hi, Niek: | || | || I am sure it is a precise work. I am so sorry that I did not get it | || yet. Could you please show me the whole process in more detail | || including which keys to use? I apologize to give you a lot of troubles. | || | || Thanks, | || | || George | || | || | || | | | | | |
How to populate a summary page ????????????????????????????????????????
Hi George,
Do you have row before you enter either 0 or one 1 into column A of that row. If so you can write code for Worksheet_Change event. In that event you check the content of the cell that is changed if it is 1 than you copy the contents and than switch to sheet2 and paste whatever you coppied. This scheme assumes that you just copy one row whose first cell is the one you just edited. You can use the following code: However you need to take the necessary precautions not to copy the same row more than once. Cause change event fires whenever you leave the current cell Omer Private Sub Worksheet_Change(ByVal Target As Range) Dim bytData As Byte Dim intCol As Integer Dim lngRow As Long Dim rngCur As Range With Target intCol = .Column If intCol < 1 Then Exit Sub ' We dont want todo any thing other then first column lngRow = .Row End With bytData = Target.Value If bytData = 1 Then Set rngCur = Range("A1").CurrentRegion.Rows(lngRow) ' Assumin g you have data startin from a1 rngCur.Copy CopyToMySht "Sheet2", "A1", xlPasteAll ' This always adds after the end row of currentregion End If End Sub Sub CopyToMySht(mySht As String, _ Off_Set As String, pMethode As XlPasteType) Dim rCnt As Long Dim rngPaste As Range With Sheets(mySht) If .Range(Off_Set) < "" Then rCnt = .Range(Off_Set).CurrentRegion.Rows.Count Set rngPaste = .Range(Off_Set).Offset(rCnt) Else Set rngPaste = .Range(Off_Set) End If End With rngPaste.PasteSpecial Paste:=pMethode, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False Application.CutCopyMode = False End Sub "George" wrote in message oups.com... Hi, Omer: Thanks for your help! What I need is an automatic process. I do not like to do it manually. The only thing I would like to see is whenever I input 0 or 1 under column A on sheet 1, all rows with input 1 will go to sheet 2 IN THE SAME ORDER. I would appricate your efforts if you have an alternative to achieve this. George Ömer Ayzan wrote: Hi George If I were you I would have filtered the rows that contain 1 and copy the filtered range and paste it to the second sheet. The follovin code might do the job: Sub Test dim rngCur as range ' Current range Sheets("Sheet 1").activate ' assuming that you have data starting from A1 Range("A1").AutoFilter Field:=1, Criteria1:="1", Operator:=xlAnd Set rngCur= range("A1").CurrentRegion rngCur.Copy Sheets("Sheet 2").activate range("A1").Select Selection.PasteSpecial Paste:= xlPasteAll end sub Ömer Ayzan "George" wrote in message oups.com... Dear All: I have a excel workbook which contains two worksheets. Sheet 1 is my input page. Sheet 2 is my summary page. When my BINARY input numbers in any row under column A which is under sheet 1 are equal to 1, I would like the whole row to be copied to sheet 2. When my input numbers in any row under column A which is under sheet 1 are equal to 0, the whole row will NOT be copied to sheet 2. However, I don't like any gaps in sheet 2 AND I would like to keep the same order in sheet 2 as in sheet 1. In addition, I would like this to be done in an automatic procedure. No autofilter or any other manual efforts. The following is an illustration of what I would like to see: Under sheet 1 Column A ColumnB Column C ...... row1 1 Text1 1 row2 0 Text2 2 row3 0 Text3 3 row4 1 Text4 4 Since there are inputs in cell A1 and A2, the whole row 1 and row 2 will be copied AUTOMATICALLY to sheet 2 in the same order WITHOUT any gap as follows: Under sheet 2 ColumnA Column B Column C row1 1 Text1 1 row2 1 Text4 4 Anybody knows how to achieve this? It is my headache. I really appreciate your help! Thank you so much and I am looking forward to hearing from you soon! George |
| All times are GMT +1. The time now is 12:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com