Formulas with Cut Cells

[mpquin]

I am working off of a spreadsheet that has 3 worksheets. I need to "cut" cells from sheet 3 (so they are no longer there) and paste them to sheet 1. I have a formula in sheet 2 that pulls that value from the cell on sheet 1 (that was just cut/paste).

After the cell on sheet 1 is pasted, it changes the formula on page 2 to "#REF!". If I were to copy the cell from sheet 3 instead of paste, the formula is perfect.

Is there an option to allow me to cut/paste so the formula remains true?

[Ikaabod]

Here is an example of how you might accomplish this.

Please Login or Register  to view this content.

Where the "indirect" reads from the cell specified in the ranges D4, D5, and D6 which are specifying the ranges A4, A5, and A6 from sheet1.

The above example is in 8 different cells... you should be able to copy and then pasteSpecial (text) into cell C3 on your spreadsheet to see how it works.