'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

'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

'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

'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

'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