|
Nested IF compatability error with Excel 2007
I am using Excel 2007, co-workers still on Excel 2003. When I work on
something, I save to 97-2003 .xls to be able to share files. I only use Excel 2007 file save when I am downloading data from a database that needs to use more rows..anyway...I had a file someone shared with me (created in Excel 2003) and it had a nested if statement with 11 levels. I used the file but when I went to save it as 97-2003, I received a compatability warning about only allowing 7 levels..is that due to versions 97, 2000, 2002 of Excel?? (Oddly enought, if I save to 95/5.0, it doesn't give me that compatability message) |
Nested IF compatability error with Excel 2007
2003 does only allow up to 7 levels of IF.
May need to use a different formula to resolve your issue. VB with a CASE...SELECT statement at worst... "Ed C" wrote: I am using Excel 2007, co-workers still on Excel 2003. When I work on something, I save to 97-2003 .xls to be able to share files. I only use Excel 2007 file save when I am downloading data from a database that needs to use more rows..anyway...I had a file someone shared with me (created in Excel 2003) and it had a nested if statement with 11 levels. I used the file but when I went to save it as 97-2003, I received a compatability warning about only allowing 7 levels..is that due to versions 97, 2000, 2002 of Excel?? (Oddly enought, if I save to 95/5.0, it doesn't give me that compatability message) |
Nested IF compatability error with Excel 2007
|
Nested IF compatability error with Excel 2007
Thanks everyone...of course, I have already modified the formula to a vlookup
which works just fine...just was trying to understand which version of Microsoft Excel was the culprit in terms of limiting the nested if statement levels to 7 or less...the file that had the issue was one that I inherited, created by someone in Excel 2003...so I am guessing Excel 2003 was able to handle more than 7 nested if levels. The person who originally gave me the file to work on with the nested if statements (to whom I have returned the updated file) was wondering why I needed to update their file to get rid of the nested if 7+ level formula...didn't really have a good answer other than when I tried to save to an Excel 97-2003 from Excel 2007, I got compatability warnings. Still not really getting the answer but do appreciate the suggestions. "Ron Rosenfeld" wrote: On Mon, 6 Oct 2008 14:36:32 -0700, Ed C <Ed wrote: I am using Excel 2007, co-workers still on Excel 2003. When I work on something, I save to 97-2003 .xls to be able to share files. I only use Excel 2007 file save when I am downloading data from a database that needs to use more rows..anyway...I had a file someone shared with me (created in Excel 2003) and it had a nested if statement with 11 levels. I used the file but when I went to save it as 97-2003, I received a compatability warning about only allowing 7 levels..is that due to versions 97, 2000, 2002 of Excel?? (Oddly enought, if I save to 95/5.0, it doesn't give me that compatability message) It would be interesting to try it and see what happens. I believe Harlan has written that the problem with the nesting limitation in pre-Excel 2007 lies with the formula parser. But that formulas with more nesting than the seven can be developed in Open Office, and then saved and re-opened in earlier versions of Excel. If that also holds true for Excel 2007, the end result may be that your colleagues will be able to use the sheet, but, of course, they may not be able to edit that formula. --ron |
Nested IF compatability error with Excel 2007
In Excel 2007 you can nest up to 64 levels. In all other versions you can
nest up to 7 levels. You said this did work in Excel 95, I can't remember that far back! -- Biff Microsoft Excel MVP "Ed C" wrote in message ... Thanks everyone...of course, I have already modified the formula to a vlookup which works just fine...just was trying to understand which version of Microsoft Excel was the culprit in terms of limiting the nested if statement levels to 7 or less...the file that had the issue was one that I inherited, created by someone in Excel 2003...so I am guessing Excel 2003 was able to handle more than 7 nested if levels. The person who originally gave me the file to work on with the nested if statements (to whom I have returned the updated file) was wondering why I needed to update their file to get rid of the nested if 7+ level formula...didn't really have a good answer other than when I tried to save to an Excel 97-2003 from Excel 2007, I got compatability warnings. Still not really getting the answer but do appreciate the suggestions. "Ron Rosenfeld" wrote: On Mon, 6 Oct 2008 14:36:32 -0700, Ed C <Ed wrote: I am using Excel 2007, co-workers still on Excel 2003. When I work on something, I save to 97-2003 .xls to be able to share files. I only use Excel 2007 file save when I am downloading data from a database that needs to use more rows..anyway...I had a file someone shared with me (created in Excel 2003) and it had a nested if statement with 11 levels. I used the file but when I went to save it as 97-2003, I received a compatability warning about only allowing 7 levels..is that due to versions 97, 2000, 2002 of Excel?? (Oddly enought, if I save to 95/5.0, it doesn't give me that compatability message) It would be interesting to try it and see what happens. I believe Harlan has written that the problem with the nesting limitation in pre-Excel 2007 lies with the formula parser. But that formulas with more nesting than the seven can be developed in Open Office, and then saved and re-opened in earlier versions of Excel. If that also holds true for Excel 2007, the end result may be that your colleagues will be able to use the sheet, but, of course, they may not be able to edit that formula. --ron |
Nested IF compatability error with Excel 2007
Suprisingly....you say the nested if didn't work in prior version beyond 7
levels...but the file I received was created in Excel 2003 and had 11 levels...seemed to work fine with the originator's Excel 2003...guess it is a mystery...when the Excel 2003 person opened the file after I saved it to a 97-2003 file, their nested ifs cells no longer had the 11 level nested if formula but had numeric values yet the cell really contained a #VALUE that they didn't see until they updated the particular cell to look for their nested if...very spooky! "T. Valko" wrote: In Excel 2007 you can nest up to 64 levels. In all other versions you can nest up to 7 levels. You said this did work in Excel 95, I can't remember that far back! -- Biff Microsoft Excel MVP "Ed C" wrote in message ... Thanks everyone...of course, I have already modified the formula to a vlookup which works just fine...just was trying to understand which version of Microsoft Excel was the culprit in terms of limiting the nested if statement levels to 7 or less...the file that had the issue was one that I inherited, created by someone in Excel 2003...so I am guessing Excel 2003 was able to handle more than 7 nested if levels. The person who originally gave me the file to work on with the nested if statements (to whom I have returned the updated file) was wondering why I needed to update their file to get rid of the nested if 7+ level formula...didn't really have a good answer other than when I tried to save to an Excel 97-2003 from Excel 2007, I got compatability warnings. Still not really getting the answer but do appreciate the suggestions. "Ron Rosenfeld" wrote: On Mon, 6 Oct 2008 14:36:32 -0700, Ed C <Ed wrote: I am using Excel 2007, co-workers still on Excel 2003. When I work on something, I save to 97-2003 .xls to be able to share files. I only use Excel 2007 file save when I am downloading data from a database that needs to use more rows..anyway...I had a file someone shared with me (created in Excel 2003) and it had a nested if statement with 11 levels. I used the file but when I went to save it as 97-2003, I received a compatability warning about only allowing 7 levels..is that due to versions 97, 2000, 2002 of Excel?? (Oddly enought, if I save to 95/5.0, it doesn't give me that compatability message) It would be interesting to try it and see what happens. I believe Harlan has written that the problem with the nesting limitation in pre-Excel 2007 lies with the formula parser. But that formulas with more nesting than the seven can be developed in Open Office, and then saved and re-opened in earlier versions of Excel. If that also holds true for Excel 2007, the end result may be that your colleagues will be able to use the sheet, but, of course, they may not be able to edit that formula. --ron |
Nested IF compatability error with Excel 2007
Odd since Excel 2003 help file states can only handle up to 7 nested if's.
Can only guess as to how that could possibly been bypassed. "Ed C" wrote: Suprisingly....you say the nested if didn't work in prior version beyond 7 levels...but the file I received was created in Excel 2003 and had 11 levels...seemed to work fine with the originator's Excel 2003...guess it is a mystery...when the Excel 2003 person opened the file after I saved it to a 97-2003 file, their nested ifs cells no longer had the 11 level nested if formula but had numeric values yet the cell really contained a #VALUE that they didn't see until they updated the particular cell to look for their nested if...very spooky! "T. Valko" wrote: In Excel 2007 you can nest up to 64 levels. In all other versions you can nest up to 7 levels. You said this did work in Excel 95, I can't remember that far back! -- Biff Microsoft Excel MVP "Ed C" wrote in message ... Thanks everyone...of course, I have already modified the formula to a vlookup which works just fine...just was trying to understand which version of Microsoft Excel was the culprit in terms of limiting the nested if statement levels to 7 or less...the file that had the issue was one that I inherited, created by someone in Excel 2003...so I am guessing Excel 2003 was able to handle more than 7 nested if levels. The person who originally gave me the file to work on with the nested if statements (to whom I have returned the updated file) was wondering why I needed to update their file to get rid of the nested if 7+ level formula...didn't really have a good answer other than when I tried to save to an Excel 97-2003 from Excel 2007, I got compatability warnings. Still not really getting the answer but do appreciate the suggestions. "Ron Rosenfeld" wrote: On Mon, 6 Oct 2008 14:36:32 -0700, Ed C <Ed wrote: I am using Excel 2007, co-workers still on Excel 2003. When I work on something, I save to 97-2003 .xls to be able to share files. I only use Excel 2007 file save when I am downloading data from a database that needs to use more rows..anyway...I had a file someone shared with me (created in Excel 2003) and it had a nested if statement with 11 levels. I used the file but when I went to save it as 97-2003, I received a compatability warning about only allowing 7 levels..is that due to versions 97, 2000, 2002 of Excel?? (Oddly enought, if I save to 95/5.0, it doesn't give me that compatability message) It would be interesting to try it and see what happens. I believe Harlan has written that the problem with the nesting limitation in pre-Excel 2007 lies with the formula parser. But that formulas with more nesting than the seven can be developed in Open Office, and then saved and re-opened in earlier versions of Excel. If that also holds true for Excel 2007, the end result may be that your colleagues will be able to use the sheet, but, of course, they may not be able to edit that formula. --ron |
| All times are GMT +1. The time now is 10:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com