slow formula. Is there a faster way?

[TimvMechelen]

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:

Public Function ToColletter(Collet)    ToColletter = Split(Cells(1, Collet).Address, "$")(1)
End Function

 

[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))

 

[steve the fish]

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.

 

[TimvMechelen]

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.

 

[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?

 

[TimvMechelen]

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.

 

[steve the fish]

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.