How do I automatically put like data into several worksheets?
 

I have a worksheet with information that I want to copy automatically into
columns on another worksheet. How do I do this? I got the formula that
someone gave out a couple of years ago:

=IF(Sheet1!A3<"",Sheet1!A3,"")

But I don't know how to use it. When I put this formula into the cell where
I want the copying to start, nothing happens, except the cell shows a green
upper left corner and the following appears in the cell: # REF !

Does Sheet1 have to equal the name I've actually given to my sheet (I've
changed the name of the original worksheet on the tab at the bottom)?

Can I start the copying of data on row 3 or do I have to start with row 1?

How do I automatically put like data into several worksheets?
 

Does Sheet1 have to equal the name I've actually given to my sheet (I've
changed the name of the original worksheet on the tab at the bottom)?

Yes. Imperative.

In say: Sheet2 (or any sheet other than: Sheet1)
you could put your formula in B2:
=IF(Sheet1!A3<"",Sheet1!A3,"")
and then copy B2 down to return the values from Sheet1's A4, A5, etc

If there's a space in the sheetname, for eg suppose the name is instead:
Sheet 1, then you need to use this slight revision with apostrophes:
=IF('Sheet 1'!A3<"",'Sheet 1'!A3,"")

As a general guide, use the latter (with apostrophes) which works across the
board for all sheetnames, with or without spaces

Can I start the copying of data on row 3 or do I have to start with row 1?

No, you can change the formula to suit

Example, if you want to copy down from Sheet 1's A1 instead,
then just use this in the top cell, eg: in B2
=IF('Sheet 1'!A1<"",'Sheet 1'!A1,"")
Then copy B2 down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,900 Files:354 Subscribers:53
xdemechanik
---
"LindaSP" wrote:
I have a worksheet with information that I want to copy automatically into
columns on another worksheet. How do I do this? I got the formula that
someone gave out a couple of years ago:

=IF(Sheet1!A3<"",Sheet1!A3,"")

But I don't know how to use it. When I put this formula into the cell where
I want the copying to start, nothing happens, except the cell shows a green
upper left corner and the following appears in the cell: # REF !

Does Sheet1 have to equal the name I've actually given to my sheet (I've
changed the name of the original worksheet on the tab at the bottom)?

Can I start the copying of data on row 3 or do I have to start with row 1?

How do I automatically put like data into several worksheets?
 

See reply to your first post.

Best to stick with the same thread to save time and effort of responders.


Gord Dibben MS Excel MVP

On Wed, 16 Jul 2008 00:03:51 -0700, LindaSP
wrote:

I have a worksheet with information that I want to copy automatically into
columns on another worksheet. How do I do this? I got the formula that
someone gave out a couple of years ago:

=IF(Sheet1!A3<"",Sheet1!A3,"")

But I don't know how to use it. When I put this formula into the cell where
I want the copying to start, nothing happens, except the cell shows a green
upper left corner and the following appears in the cell: # REF !

Does Sheet1 have to equal the name I've actually given to my sheet (I've
changed the name of the original worksheet on the tab at the bottom)?

Can I start the copying of data on row 3 or do I have to start with row 1?