Shorten Formula

[SanCarlosCyclist]

Hi, I get a message in excel that a formula is too long. Below is the
formula. Is there a way to make this formula shorter with the same
results? As always, I very much appreciate your help.

=(LOOKUP(1E+100,1/'K:\Hosp\General\OUR Proposal 1\SAM\HMO\[General OUR
Proposal 1SAM v9.3 HMO Year1.xls]Inpatient Summary'!$L$11:$L$655,'K:
\Hosp\General\OUR Proposal 1\SAM\HMO\[General OUR Proposal 1SAM v9.3
HMO Year1.xls]Inpatient Summary'!$L$11:$L$655)+LOOKUP(1E+100,1/'K:\Hosp
\General\OUR Proposal 1\SAM\HMO\[General OUR Proposal 1SAM v9.3 HMO
Year1.xls]Outpatient Summary'!$K$11:$K$655,'K:\Hosp\General\OUR
Proposal 1\SAM\HMO\[General OUR Proposal 1SAM v9.3 HMO
Year1.xls]Outpatient Summary'!$K$11:$K$655))/(LOOKUP(1E+100,1/'K:\Hosp
\General\OUR Proposal 1\SAM\HMO\[General OUR Proposal 1SAM v9.3 HMO
Year1.xls]Inpatient Summary'!$I$11:$I$655,'K:\Hosp\General\OUR
Proposal 1\SAM\HMO\[General OUR Proposal 1SAM v9.3 HMO
Year1.xls]Inpatient Summary'!$I$11:$I$655)+LOOKUP(1E+100,1/'K:\Hosp
\General\OUR Proposal 1\SAM\HMO\[General OUR Proposal 1SAM v9.3 HMO
Year1.xls]Outpatient Summary'!$H$11:$H$655,'K:\Hosp\General\OUR
Proposal 1\SAM\HMO\[General OUR Proposal 1SAM v9.3 HMO
Year1.xls]Outpatient Summary'!$H$11:$H$655))

 

[Dave Peterson]

I would either put a formula in a cell in each of those sending workbooks that
did the same thing, then retrieve the value from that cell.

Or I'd use several cells with smaller formulas in them, then combine/add them in
another cell.

Hi, I get a message in excel that a formula is too long. Below is the
formula. Is there a way to make this formula shorter with the same
results? As always, I very much appreciate your help.

=(LOOKUP(1E+100,1/'K:\Hosp\General\OUR Proposal 1\SAM\HMO\[General OUR
Proposal 1SAM v9.3 HMO Year1.xls]Inpatient Summary'!$L$11:$L$655,'K:
\Hosp\General\OUR Proposal 1\SAM\HMO\[General OUR Proposal 1SAM v9.3
HMO Year1.xls]Inpatient Summary'!$L$11:$L$655)+LOOKUP(1E+100,1/'K:\Hosp
\General\OUR Proposal 1\SAM\HMO\[General OUR Proposal 1SAM v9.3 HMO
Year1.xls]Outpatient Summary'!$K$11:$K$655,'K:\Hosp\General\OUR
Proposal 1\SAM\HMO\[General OUR Proposal 1SAM v9.3 HMO
Year1.xls]Outpatient Summary'!$K$11:$K$655))/(LOOKUP(1E+100,1/'K:\Hosp
\General\OUR Proposal 1\SAM\HMO\[General OUR Proposal 1SAM v9.3 HMO
Year1.xls]Inpatient Summary'!$I$11:$I$655,'K:\Hosp\General\OUR
Proposal 1\SAM\HMO\[General OUR Proposal 1SAM v9.3 HMO
Year1.xls]Inpatient Summary'!$I$11:$I$655)+LOOKUP(1E+100,1/'K:\Hosp
\General\OUR Proposal 1\SAM\HMO\[General OUR Proposal 1SAM v9.3 HMO
Year1.xls]Outpatient Summary'!$H$11:$H$655,'K:\Hosp\General\OUR
Proposal 1\SAM\HMO\[General OUR Proposal 1SAM v9.3 HMO
Year1.xls]Outpatient Summary'!$H$11:$H$655))