I have 4 tabs in the SS. I have cells in tabs 2-4 linked to tab 1. If i insert n

Question

I have 4 tabs in the SS. I have cells in tabs 2-4 linked to tab 1. If i insert new rows on tab 1 my linked tabs are skipping the new inserted rows.

So...say Tab2 rows 4-200 are linked to Tab1, but when a user adds rows on Tab1, Tab2 just omits the new rows. (Rows are added to Tab1 as SS is used). I need Tab2 to link to Tab1 AND include any new inserted rows. (These 2 tabs do NOT look identical and there is much conditional formating on Tab2)

Hope this was enough info....

Explanation / Answer

Tap Alt+F11 and when the VBE opens, immediately use the pull-down mneus to Insert, Module (e.g. Alt+I, M). Paste the following into the new pane titled something like Book1 - Module1 (Code),

Sub make_INDIRECT()
Dim r As Range
For Each r In Selection
r.Formula = Replace(r.Formula, "=", "=indirect(""") & """)"
Next r
End Sub

Tap Alt+Q to return to your worksheet. Select the references you want to convert to =INDIRECT(...) and then tap Alt+F8 and Run the macro.

This method presupposes that all of the references are correct to begin with.

Addendum: the INDIRECT() function is considered volatile . This means that each of them will be recalculated whenever anything changes within your workbook and initiates a calculation cycle. With 5000 of these, you may experience some calculation lag when you have the workbook/worksheet set to Automatic Calculation (Formulas, Calculation, Calculation Options).

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.