Help with Improving the Formula Speed?

[mazher]

Hi Everyone,

I need some help please.

I have a Summary Sheet in which I am getting the information form other worksheets ( Sheet Name which is a customer name, date of last payment, last payment amount)

At the moment I have approx 50 sheets but will increase to approx 175 (one sheet for each customer)

In the summary sheet I am using the following formulas

A3 (Index)= 2,3,4,.........
B3(Sheet Name) =INDEX(SheetNames,A3)
C3(Link)=HYPERLINK("#'"&B3&"'!A1","Go To Sheet")
D3(Date last payment Rcvd) = MAX(INDIRECT("'" & B3 & "'!D:D"))
E3(Last Amount Received) =LOOKUP(2,1/(ISNUMBER(INDIRECT("'"&B3&"'!J:J"))),INDIRECT("'"&B3&"'!J:J"))

Even with 50 sheets its slowing down and whenever i edit and change anything in the any worksheet it takes so much time in calculation.

The file size is only 136 KB,

Any help in modifying the above formulas to speed up the calculation will be highly appreciated.

 

[DanteAmor]

Try the following with this pair of formulas:

D3(Date last payment Rcvd) = MAX(INDIRECT("'" & B3 & "'!D:D"))
E3(Last Amount Received) =LOOKUP(2,1/(ISNUMBER(INDIRECT("'"&B3&"'!J:J"))),INDIRECT("'"&B3&"'!J:J"))

Instead of using the entire column.
Specifies a range of rows you have plus line slack for future growth.

If you currently have a thousand lines, put for example two thousand lines:

D3(Date last payment Rcvd) = MAX(INDIRECT("'" & B3 & "'!$D$1:$D$2000"))
E3(Last Amount Received) =LOOKUP(2,1/(ISNUMBER(INDIRECT("'"&B3&"'!$J$1:$J$2000"))),INDIRECT("'"&B3&"'!$J$1:$J$2000"))

Try and tell us.

 

[mazher]

Try the following with this pair of formulas:

D3(Date last payment Rcvd) = MAX(INDIRECT("'" & B3 & "'!D:D"))
E3(Last Amount Received) =LOOKUP(2,1/(ISNUMBER(INDIRECT("'"&B3&"'!J:J"))),INDIRECT("'"&B3&"'!J:J"))

Instead of using the entire column.
Specifies a range of rows you have plus line slack for future growth.

If you currently have a thousand lines, put for example two thousand lines:

D3(Date last payment Rcvd) = MAX(INDIRECT("'" & B3 & "'!$D$1:$D$2000"))
E3(Last Amount Received) =LOOKUP(2,1/(ISNUMBER(INDIRECT("'"&B3&"'!$J$1:$J$2000"))),INDIRECT("'"&B3&"'!$J$1:$J$2000"))

Try and tell us.

Thanks.

The maximum rows I have used in the sheet is less than 500, I modified the formula by referencing to only 500 rows and it made a lot of difference. ( Its flying as compared to what it was doing previously)

Extremely thankful for your suggestion.

 

[DanteAmor]

Please, you should mark the post that solved your question instead of marking your own answer as a solution.

Im glad to help you. Thanks for the feedback.

 

[Dave Patton]

Try the following

T202307a.xlsm
	A	B	C
1
2
3		1d	100
1e

Cell Formulas
Range	Formula
C3	C3	=LOOKUP(BigNum,INDIRECT("'"&B3&"'!$J$1:$J$500"))

N.B.
The formula uses a named value that is named BigNum

With Formula Name Manager
Name BigNum
Value =9.99999999999999E+307



This is an extremely large number