Sorry, but you've already found what's probably the most efficient way of
doing this without resorting to VBA and a macro to do the copying.

You can either have a value or a formulat that gives a value in a cell, not
both.

"Huber57" wrote:

I have a list of data on Sheet1. I would like to pull these (using a
formula) to Sheet2. Further, I would like the data to remain in Sheet2 even
if I delete the information in Sheet1.

Is there any way I can write a formula to accomplish this? I know that
copying and pasting "values" will do the trick but I would like to use a
formula. I have tried using intentional circular references (like with a
timestamp) but to no avail.

Help?
Sincerely,
Doug