slow formula. Is there a faster way?
Results 1 to 7 of 7

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

  1. #1
    Board Regular
    Join Date
    Nov 2016
    Posts
    114
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,554
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default 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))
    Looking for opportunities

  3. #3
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,554
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default 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.
    Looking for opportunities

  4. #4
    Board Regular
    Join Date
    Nov 2016
    Posts
    114
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: slow formula. Is there a faster way?

    Quote Originally Posted by steve the fish View Post
    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. #5
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,554
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default 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?
    Looking for opportunities

  6. #6
    Board Regular
    Join Date
    Nov 2016
    Posts
    114
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: slow formula. Is there a faster way?

    Quote Originally Posted by steve the fish View Post
    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. #7
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,554
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default 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.
    Looking for opportunities

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •