Excel nested IF

Microsoft Excel for mac 2001
September 23, 2010 at 17:52:16
Specs: Macintosh
 I am trying create a spreadsheet that tracks various project financial performance over a three year period. There are a total of 8 types of projects (A-G and X) starting at various times over a three year period, for example Project A is month 1, B in 3, G in 4, A again in 5...etc. I am trying to maintain flexibility to change projects data (for # of projects and timing). I am using the following nested formula, but run out of capacity before finishing:=IF(F\$8<\$D11,0,IF(\$C11="A",OFFSET('A Title'!\$F\$53,0,'Timing Table'!F\$8-'Timing Table'!\$D11),IF(\$C11="B",OFFSET('B Title'!\$F\$53,0,'Timing Table'!F\$8-'Timing Table'!\$D11),IF(\$C11="C",OFFSET('C Title'!\$F\$53,0,'Timing Table'!F\$8-'Timing Table'!\$D11),IF(\$C11="D",OFFSET('D Title'!\$F\$53,0,'Timing Table'!F\$8-'Timing Table'!\$D11),IF(\$C11="E",OFFSET('E Title'!\$F\$53,0,'Timing Table'!F\$8-'Timing Table'!\$D11),IF(\$C11="F",OFFSET('F Title'!\$F\$53,0,'Timing Table'!F\$8-'Timing Table'!\$D11),"NA")))))))Where F\$8 is the current month and \$D11 is the month start date for a particular project. \$C11 is the cell in which the project type is defined. I tried to define two formulas (AtoF and GtoX) and tried the formula (Iff\$8<\$d11,0,if(AtoF,AtoF,GtoX), but the inputs cells for each defined formula seemed to remain fixed and would not change across the time range.Any help greatly appreciated

See More: Excel nested IF

#1
September 23, 2010 at 17:53:54
 I am using Microsoft Excel for Mac 2004, it that makes a difference

Report •

#2
September 23, 2010 at 19:22:43
 If I'm not mistaken, the only thing that changes is the first letter of the sheet names and you're checking for the first letter in C11 each time.A TitleB Titleetc.Why not eliminate all the IF that check C11 and use the text in C11 to build the sheet name via the INDIRECT function?(For ease of testing, I used 1 as the cols argument for OFFSET. You, or course would use your Timing Table calculation.)'A Title'!\$F\$53 becomes INDIRECT("'" & C11 &" Title'!\$F\$53"Your entire formula becomes;=IF(F\$8<\$D11, 0, OFFSET(INDIRECT("'" & C11 &" Title'!\$F\$53"), 0, 1))This will take the value in C11 and construct a sheet name with it.Look up the INDIRECT function in Excel help to see how it works. It basically takes a text argument and turns it into a cell reference. Note the Double Quote - Single Quote - Double Quote at the beginning of the string. The various parts of the text string must be enclosed in Double Quotes, including the Single Quote before the sheet name.

Report •

#3
September 24, 2010 at 08:35:13
 Worked perfectly and rather elegant construction. Thanks!

Report •

Related Solutions

#4
September 24, 2010 at 08:58:57
 Thanks! I kind of liked that one myself. ;-)

Report •