Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying a Range of Cells having data only
Hi I had posted my question in one of other Excel groups but haven't got a solution yet. So am trying out here too. I receive an Excel file from my Materials dept on a regular basis for keeping track of Purchase orders, whose fields get populated progressively by date. The workbook contents is similar to the following Date Bin No. Order no. 02-dec-08 SB3241 SS/432 05-dec-08 SC1157 SX/201 07-dec-08 SB0134 SS/456 ...and so on and so forth. There is another Excel workbook where I copy and paste the above contents. There are other fields in the 2nd workbook, used for analysing the full data. Now everytime a new date/or new Purchase Order gets populated in the first Excel file, I copy that corresponding row/rows and paste it at the desired location, just a row below from previously copied data in the 2nd Excel file. So everytime, I have to check whether there is a new entry in the first book, and then copy row by row to the 2nd excel book. Is there any function by which after I open the first Excel book, Excel understands the range of cells in which data is there and copies the particular range of cells for pasting it to the 2nd Excel file. For eg. if the initial data was in A1:C3, Excel copies this particular range. Say, after three days, there have been two more entries, and thus the data range is now A1:C5. The function now copies Range ("A1:C5").. Thks for the help Shriil |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying a Range of Cells having data only
Hi,
You could write a formula or use a macro. In the formula approach you are not really copying the data you are connecting to it. Here is the formula idea: Suppose your data starts in A1 of the first workbook and in A5 of the second workbook. Then open both files and tile the windows so you can see both. Then in A5 type =If( and then click over to the other file and select cell A1 type ="","", And then click in the second workbook on cell A1 again, press Enter. Copy this formula down and over as far as desired. The resulting formula will look something like this: =IF('[Capactiy Tracking Formulas.xls]Supply Detail & Demand Summary'!$A$1="","",'[Capactiy Tracking Formulas.xls]Supply Detail & Demand Summary'!$A$1) -- If this helps, please click the Yes button Cheers, Shane Devenshire "shriil" wrote: Hi I had posted my question in one of other Excel groups but haven't got a solution yet. So am trying out here too. I receive an Excel file from my Materials dept on a regular basis for keeping track of Purchase orders, whose fields get populated progressively by date. The workbook contents is similar to the following Date Bin No. Order no. 02-dec-08 SB3241 SS/432 05-dec-08 SC1157 SX/201 07-dec-08 SB0134 SS/456 ...and so on and so forth. There is another Excel workbook where I copy and paste the above contents. There are other fields in the 2nd workbook, used for analysing the full data. Now everytime a new date/or new Purchase Order gets populated in the first Excel file, I copy that corresponding row/rows and paste it at the desired location, just a row below from previously copied data in the 2nd Excel file. So everytime, I have to check whether there is a new entry in the first book, and then copy row by row to the 2nd excel book. Is there any function by which after I open the first Excel book, Excel understands the range of cells in which data is there and copies the particular range of cells for pasting it to the 2nd Excel file. For eg. if the initial data was in A1:C3, Excel copies this particular range. Say, after three days, there have been two more entries, and thus the data range is now A1:C5. The function now copies Range ("A1:C5").. Thks for the help Shriil |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying a Range of Cells having data only
On Feb 6, 10:05*pm, Shane Devenshire
wrote: Hi, You could write a formula or use a macro. *In the formula approach you are not really copying the data you are connecting to it. *Here is the formula idea: Suppose your data starts in A1 of the first workbook and in A5 of the second workbook. *Then open both files and tile the windows so you can see both. * Then in A5 type =If( and then click over to the other file and select cell A1 type ="","", And then click in the second workbook on cell A1 again, press Enter. Copy this formula down and over as far as desired. The resulting formula will look something like this: =IF('[Capactiy Tracking Formulas.xls]Supply Detail & Demand Summary'!$A$1="","",'[Capactiy Tracking Formulas.xls]Supply Detail & Demand Summary'!$A$1) -- If this helps, please click the Yes button Cheers, Shane Devenshire "shriil" wrote: Hi I had posted my question in one of other Excel groups but haven't got a solution yet. So am trying out here too. I receive an Excel file from my Materials dept on a regular basis for keeping track of Purchase orders, whose fields get populated progressively by date. The workbook contents is similar to the following Date * * * * * * *Bin No. * * * Order no. 02-dec-08 * * SB3241 * * * SS/432 05-dec-08 * * SC1157 * * * SX/201 07-dec-08 * * SB0134 * * * SS/456 ...and so on and so forth. There is another Excel workbook *where I copy and paste the above contents. There are other fields in the 2nd workbook, used for analysing the full data. Now everytime a new date/or new Purchase Order gets populated in the first Excel file, I copy that corresponding row/rows and paste it at the desired location, just a row below from previously copied data in the 2nd Excel file. So everytime, I have to check whether there is a new entry in the first book, and then copy row by row to the 2nd excel book. Is there any function by which after I open the first Excel book, Excel understands the range of cells in which data is there and copies the particular range of cells for pasting it to the 2nd Excel file. For eg. if the initial data was in A1:C3, Excel copies this particular range. Say, after three days, there have been two more entries, and thus the data range is now A1:C5. The function now copies Range ("A1:C5").. Thks for the help Shriil- Hide quoted text - - Show quoted text - Thanks Shane. Thats a nice way to work it out. But it would be a bit tedious for copying the formula for each cell, sheet to sheet. Was just thinking if I could write a macro where when it is run, excel understands the range of cells that has the data, copies it, and pastes it at the desired location in the other Sheet. Shriil |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying a Range of Cells having data only
Just take the absolute reference out of the row portion then you can copy the
formula down. What you do is copy is as far down as you like and then when data comes in the results will display, otherwise nothing shows. =IF('[Capactiy Tracking Formulas.xls]Supply Detail & Demand Summary'!$A1="","",'[Capactiy Tracking Formulas.xls]Supply Detail & Demand Summary'!$A1) If you are copying more than one column based on the contents or lack of contents in A1 then take take the dollar sign off of the second column reference. If you want to populate a cell based on its matching cell, that is if C1="" then C1. Take the $ sign off of both column references. Now you can copy down and to the right as far as you ever expect to have data. A macro approach would depend on knowing what cell(s) should trigger the copy process. But the basic VBA code would use a Worksheet_Change event such as: Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Set isect = Application.Intersect(Target, Range("A1:A1000")) If Not isect Is Nothing Then 'Your code here End If End Sub -- If this helps, please click the Yes button Cheers, Shane Devenshire "shriil" wrote: On Feb 6, 10:05 pm, Shane Devenshire wrote: Hi, You could write a formula or use a macro. In the formula approach you are not really copying the data you are connecting to it. Here is the formula idea: Suppose your data starts in A1 of the first workbook and in A5 of the second workbook. Then open both files and tile the windows so you can see both. Then in A5 type =If( and then click over to the other file and select cell A1 type ="","", And then click in the second workbook on cell A1 again, press Enter. Copy this formula down and over as far as desired. The resulting formula will look something like this: =IF('[Capactiy Tracking Formulas.xls]Supply Detail & Demand Summary'!$A$1="","",'[Capactiy Tracking Formulas.xls]Supply Detail & Demand Summary'!$A$1) -- If this helps, please click the Yes button Cheers, Shane Devenshire "shriil" wrote: Hi I had posted my question in one of other Excel groups but haven't got a solution yet. So am trying out here too. I receive an Excel file from my Materials dept on a regular basis for keeping track of Purchase orders, whose fields get populated progressively by date. The workbook contents is similar to the following Date Bin No. Order no. 02-dec-08 SB3241 SS/432 05-dec-08 SC1157 SX/201 07-dec-08 SB0134 SS/456 ...and so on and so forth. There is another Excel workbook where I copy and paste the above contents. There are other fields in the 2nd workbook, used for analysing the full data. Now everytime a new date/or new Purchase Order gets populated in the first Excel file, I copy that corresponding row/rows and paste it at the desired location, just a row below from previously copied data in the 2nd Excel file. So everytime, I have to check whether there is a new entry in the first book, and then copy row by row to the 2nd excel book. Is there any function by which after I open the first Excel book, Excel understands the range of cells in which data is there and copies the particular range of cells for pasting it to the 2nd Excel file. For eg. if the initial data was in A1:C3, Excel copies this particular range. Say, after three days, there have been two more entries, and thus the data range is now A1:C5. The function now copies Range ("A1:C5").. Thks for the help Shriil- Hide quoted text - - Show quoted text - Thanks Shane. Thats a nice way to work it out. But it would be a bit tedious for copying the formula for each cell, sheet to sheet. Was just thinking if I could write a macro where when it is run, excel understands the range of cells that has the data, copies it, and pastes it at the desired location in the other Sheet. Shriil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying rules set on a range of cells | Excel Worksheet Functions | |||
Copying A Range of Cells to Another Worksheet | Excel Discussion (Misc queries) | |||
Copying Conditional Formatting to range of cells | Excel Worksheet Functions | |||
Problem copying formula to range of cells | Setting up and Configuration of Excel | |||
How do I skip blank cells when copying over a range of cells? | Excel Discussion (Misc queries) |