Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
'Pushing' data from 1 sheet to another
Is there a way to 'push' data from one sheet to another? I am thinking of
some kind of formula like an if-then-else. I want to check a cell on sheet 1 for a certain value (such as the number 5) and if the condition is met, then I want to copy or 'push' other cell data from sheet 1 to other sheets in the workbook. I realize I can use the 'if' function in a cell on the sheet where I want the data to end up which would 'pull' the data from sheet 1, but this will not work for me because I want to use the data in the cell on sheet 1 to determine which other sheet in the workbook the other data from sheet 1 is copied to. As an example, if the number 5 is in a cell on sheet 1, then I want to copy or 'push' other data on sheet 1 to sheet 5 in the workbook. Thanks for any help, Learner101b |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
'Pushing' data from 1 sheet to another
A formula cannot push data - it can only pull it, so you will need IF
formulae in each of your sheets as you describe near the end of your post. Pete On Jan 20, 6:58*pm, Learner101b wrote: Is there a way to 'push' data from one sheet to another? *I am thinking of some kind of formula like an if-then-else. I want to check a cell on sheet 1 for a certain value (such as the number 5) and if the condition is met, then I want to copy or 'push' other cell data from sheet 1 to other sheets in the workbook. *I realize I can use the 'if' function in a cell on the sheet where I want the data to end up which would 'pull' the data from sheet 1, but this will not work for me because I want to use the data in the cell on sheet 1 to determine which other sheet in the workbook the other data from sheet 1 is copied to. *As an example, if the number 5 is in a cell on sheet 1, then I want to copy or 'push' other data on sheet 1 to sheet 5 in the workbook. Thanks for any help, Learner101b |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
'Pushing' data from 1 sheet to another
Let's say A1 is the trigger cell and A2 is the data to be pushed to other
sheets in cell Z100 For example, if A1 is set to 5, A2 will be copied to sheet5, cell Z100. Install this small macro in the worksheet code area of sheet1: Private Sub Worksheet_Change(ByVal Target As Range) Set r1 = Range("A1") Set r2 = Range("A2") If Intersect(Target, r1) Is Nothing Then Exit Sub dsheet = "Sheet" & r1.Value Application.EnableEvents = False r2.Copy Sheets(dsheet).Range("Z100") Application.EnableEvents = True End Sub This is just demo code, you can choose the source and destination cell(s) anyway you like. REMEMBER: the worksheet code area, not a standard module. -- Gary''s Student - gsnu200765 "Learner101b" wrote: Is there a way to 'push' data from one sheet to another? I am thinking of some kind of formula like an if-then-else. I want to check a cell on sheet 1 for a certain value (such as the number 5) and if the condition is met, then I want to copy or 'push' other cell data from sheet 1 to other sheets in the workbook. I realize I can use the 'if' function in a cell on the sheet where I want the data to end up which would 'pull' the data from sheet 1, but this will not work for me because I want to use the data in the cell on sheet 1 to determine which other sheet in the workbook the other data from sheet 1 is copied to. As an example, if the number 5 is in a cell on sheet 1, then I want to copy or 'push' other data on sheet 1 to sheet 5 in the workbook. Thanks for any help, Learner101b |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
'Pushing' data from 1 sheet to another
Thanks for your help. I hate to admit it, but your solution was a little
over my head. I am a quick learner and pretty much understood your macro, but I do not know how to install a macro in an excel sheet. I have some other goals that I am not sure excel can handle, so I would appreciate your recommendations to accomplish my ultimate task. I want to create an order form on worksheet 1 that would also create a specific vendor purchase order on a separate worksheet. In other words, I want worksheet 1 to be an alphabetical list of many items that would be purchased from about 10 different vendors. When a customer puts a number in the 'quantity' column, I want that line including the item number, description, etc. to move to another worksheet that is specific to the vendor the item is ordered from. I have 2 requirements that are giving me problems in developing a design. (1) I prefer to make additions/deletions only on the main list on worksheet 1 to make updating easier rather than having to go to the vendor purchase order worksheet and make changes there as well which would need to happen if I was 'pulling' the information. (2) Some vendors will have 30 items on the main list (which will be multiple pages), but most customers will only order a couple of them at a time. I would like to keep the final vendor purchase order to a single page so I prefer to not have all 30 items listed on the purchase order with only a couple of the items having quantities in the 'order' column. In other words, I only want the ordered items to show up on the vendor purchase order. I would really like to 'push' the information from the order form on worksheet 1 to the vendor purchase orders when there is a quantity in the order column, but I do not think Excel can do this. I am a new user of this discussion group, semi-technical and use Excel 2003. I am pretty good with Excel basics, but have never filtered lists, used complex conditional formulas or functions, used pivot tables, etc. But I am willing to learn and can follow and example if it is not too complicated. Any ideas? Do you think there is a solution? Learner101b "Gary''s Student" wrote: Let's say A1 is the trigger cell and A2 is the data to be pushed to other sheets in cell Z100 For example, if A1 is set to 5, A2 will be copied to sheet5, cell Z100. Install this small macro in the worksheet code area of sheet1: Private Sub Worksheet_Change(ByVal Target As Range) Set r1 = Range("A1") Set r2 = Range("A2") If Intersect(Target, r1) Is Nothing Then Exit Sub dsheet = "Sheet" & r1.Value Application.EnableEvents = False r2.Copy Sheets(dsheet).Range("Z100") Application.EnableEvents = True End Sub This is just demo code, you can choose the source and destination cell(s) anyway you like. REMEMBER: the worksheet code area, not a standard module. -- Gary''s Student - gsnu200765 "Learner101b" wrote: Is there a way to 'push' data from one sheet to another? I am thinking of some kind of formula like an if-then-else. I want to check a cell on sheet 1 for a certain value (such as the number 5) and if the condition is met, then I want to copy or 'push' other cell data from sheet 1 to other sheets in the workbook. I realize I can use the 'if' function in a cell on the sheet where I want the data to end up which would 'pull' the data from sheet 1, but this will not work for me because I want to use the data in the cell on sheet 1 to determine which other sheet in the workbook the other data from sheet 1 is copied to. As an example, if the number 5 is in a cell on sheet 1, then I want to copy or 'push' other data on sheet 1 to sheet 5 in the workbook. Thanks for any help, Learner101b |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
'Pushing' data from 1 sheet to another
Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200765 "Learner101b" wrote: Thanks for your help. I hate to admit it, but your solution was a little over my head. I am a quick learner and pretty much understood your macro, but I do not know how to install a macro in an excel sheet. I have some other goals that I am not sure excel can handle, so I would appreciate your recommendations to accomplish my ultimate task. I want to create an order form on worksheet 1 that would also create a specific vendor purchase order on a separate worksheet. In other words, I want worksheet 1 to be an alphabetical list of many items that would be purchased from about 10 different vendors. When a customer puts a number in the 'quantity' column, I want that line including the item number, description, etc. to move to another worksheet that is specific to the vendor the item is ordered from. I have 2 requirements that are giving me problems in developing a design. (1) I prefer to make additions/deletions only on the main list on worksheet 1 to make updating easier rather than having to go to the vendor purchase order worksheet and make changes there as well which would need to happen if I was 'pulling' the information. (2) Some vendors will have 30 items on the main list (which will be multiple pages), but most customers will only order a couple of them at a time. I would like to keep the final vendor purchase order to a single page so I prefer to not have all 30 items listed on the purchase order with only a couple of the items having quantities in the 'order' column. In other words, I only want the ordered items to show up on the vendor purchase order. I would really like to 'push' the information from the order form on worksheet 1 to the vendor purchase orders when there is a quantity in the order column, but I do not think Excel can do this. I am a new user of this discussion group, semi-technical and use Excel 2003. I am pretty good with Excel basics, but have never filtered lists, used complex conditional formulas or functions, used pivot tables, etc. But I am willing to learn and can follow and example if it is not too complicated. Any ideas? Do you think there is a solution? Learner101b "Gary''s Student" wrote: Let's say A1 is the trigger cell and A2 is the data to be pushed to other sheets in cell Z100 For example, if A1 is set to 5, A2 will be copied to sheet5, cell Z100. Install this small macro in the worksheet code area of sheet1: Private Sub Worksheet_Change(ByVal Target As Range) Set r1 = Range("A1") Set r2 = Range("A2") If Intersect(Target, r1) Is Nothing Then Exit Sub dsheet = "Sheet" & r1.Value Application.EnableEvents = False r2.Copy Sheets(dsheet).Range("Z100") Application.EnableEvents = True End Sub This is just demo code, you can choose the source and destination cell(s) anyway you like. REMEMBER: the worksheet code area, not a standard module. -- Gary''s Student - gsnu200765 "Learner101b" wrote: Is there a way to 'push' data from one sheet to another? I am thinking of some kind of formula like an if-then-else. I want to check a cell on sheet 1 for a certain value (such as the number 5) and if the condition is met, then I want to copy or 'push' other cell data from sheet 1 to other sheets in the workbook. I realize I can use the 'if' function in a cell on the sheet where I want the data to end up which would 'pull' the data from sheet 1, but this will not work for me because I want to use the data in the cell on sheet 1 to determine which other sheet in the workbook the other data from sheet 1 is copied to. As an example, if the number 5 is in a cell on sheet 1, then I want to copy or 'push' other data on sheet 1 to sheet 5 in the workbook. Thanks for any help, Learner101b |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
'Pushing' data from 1 sheet to another | Excel Discussion (Misc queries) | |||
Pushing the Envelope with the RANK function | Excel Discussion (Misc queries) | |||
see chart from pushing button | Charts and Charting in Excel | |||
Pushing my luck | Excel Worksheet Functions | |||
Toggle betwen "editing" and "pushing" a button in excel | Excel Discussion (Misc queries) |