Deleting reference cell while keeping formula and contents

[Nordsman]

Hello,

I'm not sure if this is even possible, or if it requires some sort of convoluted formula or special add-in, but I will try my best to word my question in a way that makes sense.

Say I have a range of cells on Sheet 1, A1-A12. I enter data into the cells. Then on a separate sheet, Sheet 2, I enter the formula "='Sheet 1'!A1'" into cell B1 and copy that formula down to cell B12, resulting in the A1 data being displayed in B1, the A2 data being displayed in B2, and so on.

Now, I'd like to keep the formulas AND Sheet 1 A1-A12 data displayed in the cell range B1-B12, while deleting the contents of A1-A12.

Furthermore, I'd like to copy the same formulas found in cells B1-B12 (of Sheet 2) and then paste them into cells B13-B24 (of Sheet 2), so when I delete the data in A1-A12 (in Sheet 1) and replace it with new data, that new data shows up in cells B13-B24 (of Sheet 2), while the old deleted A1-A12 data is still displayed in cells B1-B12.

Once again, I realize this is probably an impossibility, but I have a little hope that there's some Excel expert out there who can solve this.

Thank you,

Nordsman

 

[offthelip]

Hi Welcome to the forum. It is quite easy to do something very close to what you have asked for using VBA. Put this code in the worksheet code for sheet1 (Right click on the sheet1 tab select view code and the paste this code into the worksheet code window). Then make changes on sheet1 A1 to A12 and the values will appear on sheet 2 B1 to 12, when you delete a value from A12 to A12 the code copies the values from B1 to B13 on sheet two ,etc, Try it and see if it is anything like what you want

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:a12")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
rowno = Target.Row
 Application.EnableEvents = False
   With Worksheets("sheet2")
   If Target.Value = "" Then  ' copy B1 to B13 etc
     .Range(.Cells(rowno + 12, 2), .Cells(rowno + 12, 2)) = .Range(.Cells(rowno, 2), .Cells(rowno, 2))
   End If
    .Range(.Cells(rowno, 2), .Cells(rowno, 2)) = Target.Value
   End With
 Application.EnableEvents = True
End If
 Application.EnableEvents = True
End Sub

.