In my opinion there is no good way to achieve what you are asking.

I suggest you consider the following options:
- 1) don't keep a separate sheet showing all the data; just remove the
filter from the filtered sheet when you want to see all the data
or 2) use a macro to copy Sheet2's contents to Sheet1 when you activate
Sheet1 or when you change Sheet2, whichever is the less frequent
occurrence
or 3) [which I don't like] use a formula on Sheet1:
=IF(ISBLANK(Sheet2!A1),"",Sheet2!A1)
copied across as many columns as will be needed and down as many rows
as will be needed.



Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup