CC10
asked on
excel slow calculation (4 processors)
Hello,
I am trying to build up a workbook and have set up a formula that seems to overload the excel calculation capacity.
the formula is:
=LOOKUP(1,1/(INDIRECT("'"&
=LOOKUP(1,1/(INDIRECT("'"&
The cell B2 corresponds to a worksheet in the workbook with the corresponding name. The cell A2 is a reference which the formula uses to find the last row in the worksheet with that reference in column H and then the result is the cell value in column X of that row.
The workbook will have about 20 worksheets with 10 columns and 4000 rows.
Is there a better way to write the formula or create a function in VBA to speed up the calculation process?
Thanks,
CC
I am trying to build up a workbook and have set up a formula that seems to overload the excel calculation capacity.
the formula is:
=LOOKUP(1,1/(INDIRECT("'"&
=LOOKUP(1,1/(INDIRECT("'"&
The cell B2 corresponds to a worksheet in the workbook with the corresponding name. The cell A2 is a reference which the formula uses to find the last row in the worksheet with that reference in column H and then the result is the cell value in column X of that row.
The workbook will have about 20 worksheets with 10 columns and 4000 rows.
Is there a better way to write the formula or create a function in VBA to speed up the calculation process?
Thanks,
CC
ASKER
I am trying to create a summary sheet showing various statistics of financial trades in the currency market. If you look at the EURUSD worksheet, you can see that in row 40, a position was opened (the sale of EUR 600,000 against the dollar), there was an addition (further sale of 300,000), row 170, and then the position was closed. Row 193. Columns M to AA calculate various statistics.
Each Trade or Transaction has a reference number. Column A in the Summary worksheet and column H in the other sheets.
The summary sheet will show the relevant statistics for the transaction as well as the actual trades. In other words, the complete history.
Since I have about 20 currency pairs and about a total of 160 transactions, I am trying to create a summary sheet that will show all the details once I have inputted all the information. Since I can update the sheets through a DDE link, the intention is that the summary sheet will show the latest open transactions on a daily basis as well.
I hope this all makes sense.
CC
The workbook may become large but I can copy out the formulas of each worksheet. I only really need the formulas in the last one or two rows.
NEW4HOUR.xlsx
Each Trade or Transaction has a reference number. Column A in the Summary worksheet and column H in the other sheets.
The summary sheet will show the relevant statistics for the transaction as well as the actual trades. In other words, the complete history.
Since I have about 20 currency pairs and about a total of 160 transactions, I am trying to create a summary sheet that will show all the details once I have inputted all the information. Since I can update the sheets through a DDE link, the intention is that the summary sheet will show the latest open transactions on a daily basis as well.
I hope this all makes sense.
CC
The workbook may become large but I can copy out the formulas of each worksheet. I only really need the formulas in the last one or two rows.
NEW4HOUR.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
All works now. Thanks very much.
CC
CC
Perhaps you can post an example with your requirements.