Want entire column show data from another column

[Chelrie]

I have two worksheets.

'IN PROGRESS' - I utilize to enter data

'REPORT - IN PROGRESS' - I utilize to create a report for a client. I want column A from this worksheet to report all of the data from the same column in 'IN PROGRESS'. This list changes in length depending on how many projects we have going at one time.

Obviously - I can just say A2=A2 from the other, and drag it down. But then empty cells say zero - and I have to set my print area. Also - once we reach the number of rows I've extended the formula to, we have to go back and alter the spreadsheet.

How can I set a formula for the worksheet to lookup an entire column?

 

[Dan Waterloo]

Try

=IF(Sheet1!A2="", "", Sheet1!A2)

You can copy this formula to every cell in the column.

 

[Andrew Poulsom]

Why do you need to duplicate your data? In case there is a valid reason, put this formula in A2:

=IF(ISBLANK(Sheet1!A2),"",Sheet1!A2)

and copy it from A3 to the bottom of the worksheet. Then (assuming you have a header in A1) define Print_Area as:

=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A)-COUNTBLANK(Sheet2!$A:$A))

 

[Chelrie]

Andrew - "IN PROGRESS' has a lot of fields that the client doesn't want to print. We're trying to avoid having to hide a bunch of columns every time we need to print a report. The theory was that if we just duplicated the data we need in the other sheet - we could avoid that. Is there a better way to do this? Willing to go read help on a technique for reporting if you have suggestions...