Replicate (populate) cells with INDIRECT Worksheet Formulas for selected range referring to same range on another sheet, so that cells will always refer to a specific source cell location even if rows/cells/columns are deleted on the source sheet.

# Replicate cells with INDIRECT Formulas

Location: http://www.mvps.org/dmcritchie/excel/replicate.htm
[View without Frames]

## Source sheet is Sheet2

 A B C D E F G H I J 1 A1-2 B1-2 C1-2 D1-2 E1-2 F1-2 G1-2 H1-2 I1-2 J1-2 2 A2-2 B2-2 83 69.00 44 F H2-2 I2-2 J2-2 3 A3-2 B3-2 60 58.00 22 G H3-2 I3-2 J3-2 4 A4-2 B4-2 78 11.00 73 D H4-2 I4-2 J4-2 5 A5-2 B5-2 99 4.00 83 H H5-2 I5-2 J5-2 6 A6-2 B6-2 33 16.00 86 G H6-2 I6-2 J6-2 7 A7-2 B7-2 66 25.00 44 A H7-2 I7-2 J7-2 8 A8-2 B8-2 12 74.00 61 H H8-2 I8-2 J8-2 9 A9-2 B9-2 80 5.00 24 G H9-2 I9-2 J9-2 10 A10-2 B10-2 C10-2 48 28.00 52 D H10-2 I10-2 J10-2 11 A11-2 B11-2 C11-2 35 88.00 20 D H11-2 I11-2 J11-2 12 A12-2 B12-2 C12-2 64 54.00 91 G H12-2 I12-2 J12-2 13 A13-2 B13-2 C13-2 54 58.00 11 E H13-2 I13-2 J13-2 14 A14-2 B14-2 C14-2 27 54.00 67 A H14-2 I14-2 J14-2

## Select an area on a new sheet and run macro

Select cells A1:G11 on Sheet3 and then run the Replicate_from macro supply the sheet2 for the source worksheet name when asked.

 A B C D E F G 1 A1-2 B1-2 C1-2 D1-2 E1-2 F1-2 G1-2 2 A2-2 B2-2 83 69.00 44 F 3 A3-2 B3-2 60 58.00 22 G 4 A4-2 B4-2 78 11.00 73 D 5 A5-2 B5-2 99 4.00 83 H 6 A6-2 B6-2 33 16.00 86 G 7 A7-2 B7-2 66 25.00 44 A 8 A8-2 B8-2 12 74.00 61 H 9 A9-2 B9-2 80 5.00 24 G 10 A10-2 B10-2 C10-2 48 28.00 52 D 11 A11-2 B11-2 C11-2 35 88.00 20 D

Delete the rows you do not want, because the INDIRECT worksheet formula is used and the cell address is within double quotes, the formulas will not adjust addresses due to insertions/deletions on the source worksheet.

 A B C D E F G 1 A1-2 B1-2 C1-2 D1-2 E1-2 F1-2 G1-2 2 A2-2 B2-2 83 69.00 44 F 3 A9-2 B9-2 80 5.00 24 G 4 A10-2 B10-2 C10-2 48 28.00 52 D 5 A11-2 B11-2 C11-2 35 88.00 20 D 6

```A1:  =IF(INDIRECT("'sheet2'!A1")="","",INDIRECT("'sheet2'!A1"))
A2:  =IF(INDIRECT("'sheet2'!A2")="","",INDIRECT("'sheet2'!A2"))
A3:  =IF(INDIRECT("'sheet2'!A9")="","",INDIRECT("'sheet2'!A9"))
A4:  =IF(INDIRECT("'sheet2'!A10")="","",INDIRECT("'sheet2'!A10"))
A5:  =IF(INDIRECT("'sheet2'!A11")="","",INDIRECT("'sheet2'!A11"))
```
The macro code:  (Reference this webpage,  Please do NOT post my macros to newsgroups)
```Option Explicit
Sub Replicate_from()
'David McRitchie, 2006-03-04,
'   http://www.mvps.org/dmcritchie/excel/replicate.htm
Dim Other_sheet As String, This_sheet As String
Dim This_range As String
Dim inner  As String
Dim cell As Range
Other_sheet = "sheetx"   '-- will use if it exists
retry:
On Error Resume Next
If Len(Worksheets(Other_sheet).Name) < 1 Then
If LCase(Other_sheet) = "sheetx" Then Other_sheet = "sheet2"
On Error GoTo 0   '-- sheet2 is a suggestion
Other_sheet = Application.InputBox("supply name " _
& "of input worksheet", "Name of input worksheet", _
Other_sheet, , , , , 2)
If Other_sheet = "" Or Other_sheet = "False" Then
MsgBox "cancelled by your command -- " & Other_sheet
Exit Sub
End If
GoTo retry
End If
On Error GoTo 0
This_sheet = ActiveSheet.Name
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets(Other_sheet).Range(This_range).Copy
Sheets(This_sheet).Activate
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
On Error Resume Next
For Each cell In Selection
inner = "INDIRECT(""'" & Other_sheet _
& "'!" & cell.Address(0, 0) & """)"
cell.Formula = "=IF(" & inner & "="""",""""," & inner & ")"
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

```
If not familiar with installation and use of macros, see Getting Started with Macros and User Defined Functions

Excel questions not directly concerning my web pages are best directed to newsgroups
such as news://msnews.microsoft.com/microsoft.public.excel.misc where Excel users all around the clock from at least 6 continents ask and answer Excel questions.  Posting suggestions and netiquette.  More information on newsgroups and searching newsgroups.    Google Groups (Usenet) Advanced Search Excel newsgroups (or search any newsgroup).