A way to dynamically reference several sheets into one?

[nixh]

I've got several sheets with important info and about once a week we have to make a new excel doc just to copy and paste relevant part from all sheets so we can print that stuff out from one sheet.
For an example, we need columns A,B,D and G for rows 30-200 from Sheet 1 and A,B,D and G for rows 40-100 from Sheet 2. I've been trying to find a way to automatically get that all one one sheet so we don't have to do that. The data in Sheets 1&2 is being changed weekly with new rows being added and some being deleted so I've been getting the REF! error with a lot of the things I've tried.

Using =INDIRECT(ADDRESS(30,1,4,,"Sheet 1")) has been working well with no REF! errors and updating well but I cant drag the formula down and have it change rows/columns its referencing automatically so I don't know where to go from here.

I'd be open to a completely different method if there's a better way or for some help with my dragging issues.

Thanks!

 

[6StringJazzer]

What cell is that INDIRECT formula in? What cells do you need to drag it to? You can probably use ROW() and COLUMN() instead of 30 and 1 to make it draggable.