# Thread: slow formula. Is there a faster way? Thanks: 0 Likes: 0

1. ## slow formula. Is there a faster way?

Hi all,

My workbook is getting very slow, especially when i insert rows or enter new data for example.
Is there a faster way to achieve the same result?

I hope the formulas are translated correctly from dutch to english.

Formula 1:
=VLOOKUP(OFFSET(A85;-1;0);'CAPACITY'!\$AM:\$BL;COLUMNS('CAPACITY'!\$AM\$16:\$BL\$16);FALSE)

Formula 2:
=SUM(INDIRECT(ToColletter(COLUMN(G86))&MATCH(OFFSET(\$A86;-2;0);\$A:\$A;0)&":"&ToColletter(COLUMN(G86))&CELL("row";G86)-2))

The "ToColletter" is a VBA function that gives the charater of the column instead of the number:
Code:
```Public Function ToColletter(Collet)    ToColletter = Split(Cells(1, Collet).Address, "\$")(1)
End Function```

2. ## Re: slow formula. Is there a faster way?

Do these work the same way?

=VLOOKUP(A84,CAPACITY!\$AM:\$BL,26,FALSE)
=SUM(INDEX(\$G:\$G:G:G,MATCH(\$A84,\$A:\$A,0),0))

3. ## Re: slow formula. Is there a faster way?

The 2nd one can be simplified to:

=SUM(\$G\$84:G84)

assuming that your lookup value is within the lookup array. If it isnt use what i gave before.

4. ## Re: slow formula. Is there a faster way?

Originally Posted by steve the fish
Do these work the same way?

=VLOOKUP(A84,CAPACITY!\$AM:\$BL,26,FALSE)
=SUM(INDEX(\$G:\$G:G:G,MATCH(\$A84,\$A:\$A,0),0))
This formulas work the same way (I think), but the problem is, that sometimes rows will be deleted, for example row 84. That's why I use the OFFSET formula.

5. ## Re: slow formula. Is there a faster way?

Cant say as i really understand that. If you delete row 84 then your vlookup formula, for example, has a different lookup value. Hows does that all work? Id suggest avoiding the deleting. Why is that necessary?

6. ## Re: slow formula. Is there a faster way?

Originally Posted by steve the fish
Cant say as i really understand that. If you delete row 84 then your vlookup formula, for example, has a different lookup value. Hows does that all work? Id suggest avoiding the deleting. Why is that necessary?
Yes, if I delete row 84, then your formula would give an error, because the same cell in row 84 doesn't exist anymore.

It is necessary, because I want to sum up hours in the formula and sometimes some tasks has to be deleted.

7. ## Re: slow formula. Is there a faster way?

You still didnt really answer what i was thinking as if you delete row 84 your formula then has a different lookup value. It may be best to give a small sample of your sheet so we could take a look.