# Indirect function is making excel file very slow.



## MandeepBajimaya (Jan 3, 2023)

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 (Jan 3, 2023)

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 (Jan 3, 2023)

6StringJazzer said:


> 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.
> ...


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 (Jan 3, 2023)

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


----------



## MandeepBajimaya (Jan 4, 2023)

6StringJazzer said:


> 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 (Jan 4, 2023)

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


----------



## MandeepBajimaya (Jan 4, 2023)

6StringJazzer said:


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


Yes, I am taking last value of the table.


----------



## 6StringJazzer (Jan 5, 2023)

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 (Jan 6, 2023)

6StringJazzer said:


> 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.
> 
> 
> ```
> ...


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


----------



## 6StringJazzer (Jan 6, 2023)

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.


----------



## MandeepBajimaya (Jan 3, 2023)

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?


----------



## MandeepBajimaya (Jan 7, 2023)

Thank you very much for your response sir. But even after adding both the codes, I am still not able to use the first code of Setformulas. I added it to module and it is shown in Macros list. But it doesn't run or show any changes.


6StringJazzer said:


> 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
> 
> 
> ```
> ...


----------



## 6StringJazzer (Sunday at 11:18 AM)

Very difficult to move forward from here without access to your file. If I can find time I will prepare a simplified mock-up and share it with you. Then you can compare my solution to how you have it implemented in your file and see if there is any difference.


----------



## 6StringJazzer (Sunday at 11:27 AM)

Here is a mock-up with exactly the same code and the first few rows and columns of your data. It works fine.









						MandeepBajimaya=update formulas.xlsm
					

Shared with Dropbox




					www.dropbox.com


----------



## MandeepBajimaya (Monday at 11:32 PM)

6StringJazzer said:


> Here is a mock-up with exactly the same code and the first few rows and columns of your data. It works fine.
> 
> 
> 
> ...


Wow, got this. Thank you so much


----------

