Selecting cells on 1 worksheet from another worksheet

[barra47]

Worksheet 1 is populate from another source.

with 4 columns, No of rows vary each time populated

ID Date Job Number Hours
1 1/01/2011 1000 5
2 2/01/2011 1000 3
3 3/01/2011 2000 13
4 5/02/2011 3000 7

On worksheet 2 I have added formula to select only Date, Job Number and Hours, colums e.g =Sheet1!B1,=Sheet1!C1,=Sheet1!D1
As the number of rows on worksheet 1 vary I have created the formula in 100's of rows. but once a row on worksheet 1 is blank I still get data filled on worksheet 2

e.g. 0/01/1900 0 0


Date Job Number Hours
1/01/2011 1000 5
2/01/2011 1000 3
3/01/2011 2000 13
5/02/2011 3000 7
0/01/1900 0 0
0/01/1900 0 0
0/01/1900 0 0
0/01/1900 0 0
0/01/1900 0 0


How do I stop this from happening.

I hope this all makes sense to you

Thanks in advance

 

[Gruuuu]

[tt]
=IF(Sheet1!B1=0,"",Sheet1!B1)
[/tt]

 

[barra47]

Thanky you Gruuuu

that worked a treat,
now this leads to another issue
when I do a Print preview of Worksheet 2, I was expecting 1 page with 4 rows of data but instead I had an additional 69 blank pages, obviously picking up on all the formula cells.

I assume Set Print area wont work as the number of rows will vary
Is there a simple answer to this apart from telling the printer which pages to print

It is obvious from my post i have not had a lot of experience with Spreadsheets

 

[SkipVought]

Hi,

QUERY your sheet to return only the data you want.

I almost NEVER EVER add formula rows in anticipation of future data.

If you are using 2007 or greater, the TABLE feature will automatically add formulas from your table as you add new rows of data. In 97-2003, it is the Data > List feature, that has the closest similar feature.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]