IF function returns #VALUE upon SAVE/REOPEN???...works fine when working in workbook.

I have a workbook where all macros and functions operate correctly until I save and reopen the file.

Upon reopen one IF stament generates #VALUE.

-------
Worksheet name: 2

=IF('2'!B$7="Chest","B",IF('2'!B$7="Back","E",IF('2'!B$7="Shoulders","D",IF('2'!B$7="Biceps","F",IF('2'!B$7="Traps","A",IF('2'!B$7="Triceps","G",IF('2'!B$7="Forearms","H",IF('2'!B$7="Lats","I",IF('2'!B$7="AbsH","J",IF('2'!B$7="AbsR","K",IF('2'!B$7="AbsP","L",IF('2'!B$7="Legs","M",""))))))))))))

-------
I have eliminated macros within the workbook as being the problem and isolated the above function.

For example:

Chest <-----B$7
B <-----B8--> =IF('2'!B$7="Chest","B",...

-------
The output B feeds into a Data Validation List on another worksheet and everything works fine until I save and reopen. I have tried on all three of my computers and different OSs and they all report the same #VALUE error in Excel 2011.

I is driving me mad to have my workbook work fine until I reopen.

Thank you in advance for any advice!

Hey Paul,

You are exactly right!!!...I reduced the IF function to 6, saved, reopened, and no more #VALUE.

Because I had to reduce the IF function, but still need all of my Nested IFs as possible output, I used your suggestion of =IFERROR... and if worked beautifully.

However, I now have a new problem and was wondering if you knew the answer:

Can I use the =IFERROR function to return a named range for the purpose of a Data Validation List.

---------
For example,
Using your above suggestion: I would enter the following function into the Data Validation list source:

=IFERROR(LOOKUP('2'!B$7,{"AbsH","AbsP","AbsR","Back","Biceps","Chest","Forearms","Lats","Legs","Shoulders","Traps","Triceps"},{"J","L","K","E","F","B","H","I","M","D","A","G"}),"")

I named the ranges J L K E F B H I M D A G... and removed the quotes (from the above equation) to try and activate them as the named range output of the Validation List.

This does not seem to work...any suggestions?

I apologize if this question is amateurish but my Excel proficiency is limited. This is the last piece of the puzzle to create my WorkBook.

Thanks again!!

Amazing!!!!

Thank you so much for the help...I was just reading about how to use =INDIRECT() in Data Validation when your answer appeared...that would have been a few hours of frustration for sure (and probably no result, haha).

Your solution is part of Workout Planner I am working on for my website (www.balancedblog.com). It will be posted within the week under Free Workout Plans if you want to view the final result.

Thanks again.

Cheers.

Hi there, you posted an excellent response to this query. I'm having a similar problem with my if statement when i close and reopen my excel workbook. This is the nested if statements.

=IF(OR(C10=0,C10=""),'Consolidated Financial Impact'!F25,IF(C10=1,SUM('Consolidated Financial Impact'!F25:G25),IF(C10=2,SUM('Consolidated Financial Impact'!F25:H25),IF(C10=3,SUM('Consolidated Financial Impact'!F25:I25),IF(C10=4,SUM('Consolidated Financial Impact'!F25:J25),IF(C10=5,SUM('Consolidated Financial Impact'!F25:K25),IF(C10=6,SUM('Consolidated Financial Impact'!F25:L25),IF(C10=7,SUM('Consolidated Financial Impact'!F25:M25),IF(C10=8,SUM('Consolidated Financial Impact'!F25:N25),IF(C10=9,SUM('Consolidated Financial Impact'!F25:O25),IF(C10=10,SUM('Consolidated Financial Impact'!F25:P25))))))))))))


i tried your lookup suggestion and did the following (below), but it doesn't work - any advice? thank you so much.

IFerror(lookup(C10,{"",0,1,2,3,4,5,6,7,8,9,10},{'Consolidated Financial Impact'!F25,'Consolidated Financial Impact'!F25,SUM('Consolidated Financial Impact'!F25:G25),SUM('Consolidated Financial Impact'!F25:H25),SUM('Consolidated Financial Impact'!F25:I25),SUM('Consolidated Financial Impact'!F25:J25),SUM('Consolidated Financial Impact'!F25:K25),SUM('Consolidated Financial Impact'!F25:L25),SUM('Consolidated Financial Impact'!F25:M25),SUM('Consolidated Financial Impact'!F25:N25),SUM('Consolidated Financial Impact'!F25:O25),SUM('Consolidated Financial Impact'!F25:P25)}),"")

Originally Posted by Paul

Hi Sjochlin,

I can't say for sure, but it may be that you have more than 7 nested IF statements. I don't know the limitations in Excel 2011 (Mac), or if you're saving the file as one of the old file types vs. one of the new ones. This formula could be made a bit more efficient and readable, however:

=IFERROR(LOOKUP('2'!B$7,{"AbsH","AbsP","AbsR","Back","Biceps","Chest","Forearms","Lats","Legs","Shoulders","Traps","Triceps"},{"J","L","K","E","F","B","H","I","M","D","A","G"}),"")

Note that the lookup values are in ascending (alphabetical) order, and the return values are ordered to match the position of the lookup value.

Hopefully that helps!