I have an extremely complex formula which works flawlessly (if ya'll
know how to reduce the size, let me know). The problem is the formula
references a lot of dynamic date ranges and some lookups. The result
of some of those calculations are proper N/A and #Ref errors. The
only way I am familar with dealing with those so they don't create
problems in my totals is to use iserr or isna with an if/then
statement that repeats the formula (once to determine an error, once
to say if it's ok...to calculate).

Since my formula is already near max length, any attempt to do that
would be too long. I know one solution is to reference another cell
with the formula, but my spreadsheet is already huge (208 columns and
53 lines) and doing that will add several megs. Is there a simpler
solution.

Here is the formula (since it's always requested):

=IF(contract_type=lists!$H$35,(Cost!CW67*(1-discount_rate)),
(IF(contract_type=lists!$H$34,
((IF(contract_fee_type=percentage_per_dollar,(1+In puts!CQ35),
1)*(((VLOOKUP($F13,(INDIRECT(CONCATENATE($C13,"_ho urly_rates"))),
(MATCH($G13,Rates!$A$119:$O$119,0)),
0)*(1+average_salary_increase)^((VLOOKUP((MID(CW
$5,6,4)),fiscal_year_code,2,0))+IF((VALUE((MID(CW
$5,3,2))))=4,1,0))*(1+VLOOKUP((IF(LEFT($G13,7)="P artner","Partner","Staff")),INDIRECT(CONCATENATE($ C13,"_fringe_rate")),MATCH(CW
$6,Rates!$A$156:$I$156,1),0))
+VLOOKUP($F13,INDIRECT((CONCATENATE($C13,"_oh_rate s"))),MATCH(CW
$6,Rates!$A$156:$I$156,1),FALSE)))*(1+
(VLOOKUP($C13,ganda_rates,MATCH(CW$6,Rates!$A$156: $I$156,1),FALSE))))
+IF(contract_fee_type=dollars_per_hour,Inputs!CQ36 ,0))*Hours!CU14),
(IF(contract_type=lists!$H$36,(VLOOKUP((VLOOKUP($B 13,'Rate Info'!$A
$8:$S$56,(MATCH(CW$6,'Rate Info'!$J$7:$S$7,1)+(COLUMNS('Rate Info'!$A
$7:$J$7)-1)),0)),tandm_rates,MATCH(Cost!CW$6,'T&M Rates'!$A$3:$K$3,1),
0)*Hours!CU14),0)))))

(if I am not the most efficient with the formulas..I apologize to the
experts). I suppose I could cut some down with a named range (maybe
even the solution to my problem)..just not smart enough how to make a
formula range dynamic like I need it.

Thanks for your help (and yeah, one day I will actually learn VB).

Paul