VLOOKUP or...
 

Ihave formula:
=VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;7;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;8;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;9;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;10;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;11;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;12;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;13;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;14;FALSE)
Is there another solution?

VLOOKUP or...
 

Hi Jaan, Try this...

1. Add the following formula to cell T4 in worksheet "MPSPLAN.xls" sheet
"MPS"; =Sum(j4:S4)
Copy it all the way to cell T349.

2. Give the spreadsheet range [MPSPLAN.xls]MPS!$D$4:$T$349 a name, say
"Table" using the following commands;

Insert/ Name/ Define

3. Your formula can be replaced by the following;
=VLOOKUP(F$2;Table;17;FALSE)

How it helps.
--
Creator


"Jaan" wrote:

Ihave formula:
=VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;7;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;8;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;9;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;10;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;11;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;12;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;13;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;14;FALSE)
Is there another solution?

VLOOKUP or...
 

Hi, but I cannot use this. "MPSPLAN" is locked file. I need sumif or
sumproduct or...

"Creator" kirjutas:

Hi Jaan, Try this...

1. Add the following formula to cell T4 in worksheet "MPSPLAN.xls" sheet
"MPS"; =Sum(j4:S4)
Copy it all the way to cell T349.

2. Give the spreadsheet range [MPSPLAN.xls]MPS!$D$4:$T$349 a name, say
"Table" using the following commands;

Insert/ Name/ Define

3. Your formula can be replaced by the following;
=VLOOKUP(F$2;Table;17;FALSE)

How it helps.
--
Creator


"Jaan" wrote:

Ihave formula:
=VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;7;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;8;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;9;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;10;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;11;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;12;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;13;FALSE)+VLOOKUP(F$2;[MPSPLAN.xls]MPS!$D$4:$S$349;14;FALSE)
Is there another solution?