Indirect function is making excel file very slow.

[MandeepBajimaya]

I have an excel file which has 200 sheets for inventory items. These are for multiple companies on same sheet . I have to prepare summary for these items in the first sheet. Since I have used a lot of indirect function, it is making file very slow. What can I do to make it faster?

 

[6StringJazzer]

Are you familiar with VBA? You could use a VBA solution to either:

• Populate the data using VBA instead of formulas. Would need to be refreshed whenever any data changes.
• Populate hard-coded formulas using VBA instead of INDIRECT. Would need to be refreshed if you add new worksheets

I would need to understand how the column maps to what cell on the worksheet you want to reference.

 

[MandeepBajimaya]

Are you familiar with VBA? You could use a VBA solution to either:

• Populate the data using VBA instead of formulas. Would need to be refreshed whenever any data changes.
• Populate hard-coded formulas using VBA instead of INDIRECT. Would need to be refreshed if you add new worksheets

I would need to understand how the column maps to what cell on the worksheet you want to reference.

Hi there,

I am not aware of VBA.
The sheet has same headings for all items, i.e., company name in heading and quantity as table.
The summary sheet has total issues of all items for each company.

 

[6StringJazzer]

The example above refers to AG1000 on the other sheet. How do you determine which cell to reference for each column?

 

[MandeepBajimaya]

The example above refers to AG1000 on the other sheet. How do you determine which cell to reference for each column?

It is continuous like AG1000, then to the right it's AH1000, then AI1000 and so on. Here, the variable is name of sheet but column reference for every sheet is same

 

[6StringJazzer]

So all the referenced data on every sheet is always in row 1000?

 

[MandeepBajimaya]

So all the referenced data on every sheet is always in row 1000?

Yes, I am taking last value of the table.

 

[6StringJazzer]

This sub will create all of the formulas. Please ask if you need details about how to install the code and how to run it.

Sub SetFormulas()

   Dim Row As Long, Column As Long
   Dim LastRow As Long
   Dim LastColumn As Long
   Dim RelativeFormula As String
   
   LastRow = Cells(Rows.Count, "D").End(xlUp).Row
   LastColumn = Cells(6, Columns.Count).End(xlToLeft).Column
   For Row = 8 To LastRow
      RelativeFormula = "='" & Cells(Row, "D") & "'!R1000C[28]"
      Range(Cells(Row, "E"), Cells(Row, LastColumn)).FormulaR1C1 = RelativeFormula
   Next Row
   
End Sub

 

[MandeepBajimaya]

This sub will create all of the formulas. Please ask if you need details about how to install the code and how to run it.

Sub SetFormulas()

   Dim Row As Long, Column As Long
   Dim LastRow As Long
   Dim LastColumn As Long
   Dim RelativeFormula As String
  
   LastRow = Cells(Rows.Count, "D").End(xlUp).Row
   LastColumn = Cells(6, Columns.Count).End(xlToLeft).Column
   For Row = 8 To LastRow
      RelativeFormula = "='" & Cells(Row, "D") & "'!R1000C[28]"
      Range(Cells(Row, "E"), Cells(Row, LastColumn)).FormulaR1C1 = RelativeFormula
   Next Row
  
End Sub

I have added this code inside Module of the sheet from Developer tab. But I am not aware how to use it.

 

[6StringJazzer]

Probably the easiest way to use it is to add a module. Right click on the project and select Insert > Module. It will be called Module1. Then add this code

Public Sub RefreshIndex()

   Worksheets("Index").SetFormulas

End Sub

From the Developer tab select Macros, click on RefreshIndex from the list, and click Run.