Spreadsheet takes long to open or does not open at all
Hi
We have couple of Excel spreadsheets which we are struggling to open. Both of them take long to open in Excel 2013 and Excel 2016 does not open it at all (or we just do not have patience waiting after it has not been responding for so long).
1st spreadsheet has few worksheets of which last three have only cells contain text (i.e. names and addresses) and the first worksheets use the following/similar formulas to pull data out of the last three worksheets:
=VLOOKUP(C50,'Tenants and Property Addresses'!$A$2:$C$220,2,0
=VLOOKUP(C50,'Tenants and Property Addresses'!$A$2:$E$220,3,0
=VLOOKUP(C80,'Tenants and Property Addresses'!$A$2:$E$220,3,0
The thing is that each worksheet which contains =VLOOKUP formulas has about 25 of them populated and other 25-20 which have the =VLOOKUP formula but are not populated since we did not use it yet and then it displays the errors because it does not contain data.
Is there a way to have the formulas in but not act until the data is entered?
Is this the right formula we are using?
Should VLOOKUP be that slow? or is there a way to make it better/faster?
2nd spreadsheet is just a typical bank reconciliation document. It has got months with transactions and then one which sums it all up. The monthly worksheets only have basic SUM formulas and numbers data, the summing up one is pulling some Totals from the monthly worksheets. I checked and the formulas and formatting are only in the cells which are being used. But I noticed that when users create new monthly worksheet, they use the previous month's one in such a way that they select all of the cells in the left top corner and then paste it into the new formula - could that be the culprit as you can see the size of the spreadsheets growing each month by 100 - 300kb.
Is there a better way to do it all?
Additionally, I would like to mention that we found some mysterious objects in both of the spreadsheets.
The first one seems to be like in front of the cells preventing from clicking on them but I am not sure what kind of object this is and how to find and delete all of them at once if not needed. See attached screenshot.
The second one was in the left top corner of the worksheet and to be honest there were quite few of them there. Again I am not sure what kind of object this is and how to find and delete all of them at once if not needed. See attached screenshot.
Any ideas and suggestions?
Regards,
Tom
1st-spreadsheet-mystery.png
2nd-spreadsheet-mystery.png
We have couple of Excel spreadsheets which we are struggling to open. Both of them take long to open in Excel 2013 and Excel 2016 does not open it at all (or we just do not have patience waiting after it has not been responding for so long).
1st spreadsheet has few worksheets of which last three have only cells contain text (i.e. names and addresses) and the first worksheets use the following/similar formulas to pull data out of the last three worksheets:
=VLOOKUP(C50,'Tenants and Property Addresses'!$A$2:$C$220,2,0
=VLOOKUP(C50,'Tenants and Property Addresses'!$A$2:$E$220,3,0
=VLOOKUP(C80,'Tenants and Property Addresses'!$A$2:$E$220,3,0
The thing is that each worksheet which contains =VLOOKUP formulas has about 25 of them populated and other 25-20 which have the =VLOOKUP formula but are not populated since we did not use it yet and then it displays the errors because it does not contain data.
Is there a way to have the formulas in but not act until the data is entered?
Is this the right formula we are using?
Should VLOOKUP be that slow? or is there a way to make it better/faster?
2nd spreadsheet is just a typical bank reconciliation document. It has got months with transactions and then one which sums it all up. The monthly worksheets only have basic SUM formulas and numbers data, the summing up one is pulling some Totals from the monthly worksheets. I checked and the formulas and formatting are only in the cells which are being used. But I noticed that when users create new monthly worksheet, they use the previous month's one in such a way that they select all of the cells in the left top corner and then paste it into the new formula - could that be the culprit as you can see the size of the spreadsheets growing each month by 100 - 300kb.
Is there a better way to do it all?
Additionally, I would like to mention that we found some mysterious objects in both of the spreadsheets.
The first one seems to be like in front of the cells preventing from clicking on them but I am not sure what kind of object this is and how to find and delete all of them at once if not needed. See attached screenshot.
The second one was in the left top corner of the worksheet and to be honest there were quite few of them there. Again I am not sure what kind of object this is and how to find and delete all of them at once if not needed. See attached screenshot.
Any ideas and suggestions?
Regards,
Tom
1st-spreadsheet-mystery.png
2nd-spreadsheet-mystery.png
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
EE requested assistance with closing this question.
ASKER