ExcelTip.com
Account Icon Account Icon Account Icon
ExcelTip.com

Go Back   Excel Help Forum > Usenet Groups > Excel 2007 Help
Formula keeps crashing excel Formula keeps crashing excel
Register FAQ Forum Rules Members List Calendar Search Today's Posts Mark Forums Read

Notices
Hi All,
The big green guy here, just wanted to let you all know that we now have the reputation system in place, the reputation system allows you to give a visible pat on the back to those that have helped you or whose information has been helpful.

You will see an icon in every post (Light Blue scales), click this to give the reputation point to whom you think deserves it, a green square appears for a reputation with more and more adding over time.Pretty soon you will see a string of green squares appear in the top right of your posts as your reputation grows and your forum presence!

Good Luck!
The Hulkster!

Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 07-24-2008, 07:56 PM
aawatta aawatta is offline
Registered User
  
Join Date: 24 Jul 2008
Location: Spokane
Posts: 3
aawatta is on a distinguished road
Unhappy Formula keeps crashing excel
Hello all, I've gotten a ton of help from these forums (never posted) but can't find a solution for this problem I'm having.

I have a workbook with the following formula:

Quote:
=IF(AND(ISNUMBER(D4),D4=0),"",IF($B4="intro",15,VLOOKUP((IF(ISBLANK(D5),$L4,(D4+$J4))),CHOOSE(IF(ISB LANK(D5),10,(D5/10)),'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!PRO10,'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!PRO20,'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!PRO30,'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!PRO40,'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!PRO50,'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!PRO60,'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!PRO70,'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!PRO80,'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!PRO90,'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!PRO100),2,TRUE)))
Prorate Sheet has multiple sheets each with 10 named tables (PRO10 to 100) which have a scope of thier perticular sheet (eg. PRO10 for sheet .20 and PRO10 for sheet .65 etc).

I enter a prorate % (as a whole number eg. 90 for 90%) and the vlookup looks up the named table (PRO90 for 90%) and returns a $ amount. The same formula is replecated 100 times (except with changes to the sheet name in the prorate workbook).

When I open up the workbook containing the formulas and I save 80% of the time it will cause excel to crash. I have tried this with excel 2003 and was able to open and save the workbook 20 times (I'd be lucky to open and save it twice in a row in 07).

Originally I had thaught the workbook might be currupted (theres 6 of them all together with the same formula) so I recreated the workbooks from scratch but after putting in the formula it started crashing again.

I have also tried saving it as 07 and the problem went away but when I resave as excel97-2003 it starts crashing again.

The only other formula in the workbook which links outside is:

Quote:
=IF(OR(ISBLANK($B4),$B4="intro"),L7,L7*VLOOKUP($B4,'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls]Prorate & Retention'!Retention,2,FALSE))
Which is repeated 20 times in the workbook. Did I reach some kind of magical formula limit?

I would be extreamly grateful if you guys could give me any advice and sorry for the long winded post.

Thank you.
Ab
Reply With Quote
  #2  
Old 07-28-2008, 11:07 AM
taiken878 taiken878 is offline
Registered User
  
Join Date: 28 Jul 2008
Location: California
Posts: 1
taiken878 is on a distinguished road
hi
Try saving the file in the Enable Macro XML format. I was having the same issues and not as bad any more.

Let me know if this works

T
Reply With Quote
  #3  
Old 07-28-2008, 01:49 PM
aawatta aawatta is offline
Registered User
  
Join Date: 24 Jul 2008
Location: Spokane
Posts: 3
aawatta is on a distinguished road
Works like a charm but it puts me in the same problem as when I save it as a regular 07 excel workbook, its got to be viewable in excel 97-03.

I might just have to save it as 07 then copy and paste values only into a 97-03 version, its a headache but I don't know of another solution.

Is VLOOKUP not supported in 03 or something?
Reply With Quote
  #4  
Old 08-04-2008, 06:20 PM
aawatta aawatta is offline
Registered User
  
Join Date: 24 Jul 2008
Location: Spokane
Posts: 3
aawatta is on a distinguished road
Well, I've saved it as a 07 macro enabled workbook (.xlsm) but when I copy the workbook and paste it to a new location (trying to keep originals blank as templates) it changes the formula by adding a ' at the start and end of the named ranges.

Before:
Quote:
=IF(AND(ISNUMBER(D4),D4=0),"",IF($B4="intro",15,VLOOKUP((IF(ISBLANK(D5),$L4,(D4+$J4))),CHOOSE(IF(ISB LANK(D5),10,(D5/10)),'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!PRO10,'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!PRO20,'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!PRO30,'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!PRO40,'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!PRO50,'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!PRO60,'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!PRO70,'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!PRO80,'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!PRO90,'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!PRO100),2,TRUE)))
After:
Quote:
=IF(AND(ISNUMBER(D4),D4=0),"",IF($B4="intro",15,VLOOKUP((IF(ISBLANK(D5),$L4,(D4+$J4))),CHOOSE(IF(ISB LANK(D5),10,(D5/10)),'\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!'PRO10','\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!'PRO20','\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!'PRO30','\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!'PRO40','\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!'PRO50','\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!'PRO60','\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!'PRO70','\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!'PRO80','\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!'PRO90','\\Spnts50fs001\E\Staff\HR\2008 Bonus\[Prorate sheet.xls].65'!'PRO100'),2,TRUE)))
Anyone know why this is? If I need to make changes to the formula on the fly I get the "your formula contains errors" error.
Reply With Quote
Reply

Bookmarks

New topics in Excel 2007 Help

« Previous Thread | Next Thread »

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off
Forum Jump


All times are GMT -4. The time now is 12:12 AM.

Contact Us - ExcelTip.com - Archive - Top

Powered by vBulletin® Version 3.7.3
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Search Engine Friendly URLs by vBSEO 3.2.0