vlookup
 

Hello to all,
I have the archives excel with 12 sheets with data
and a 13° for the totals.
I have used in 13° the sheet in several columns vlookup+ vlookup+ +vlookup+
............... in order to add the data of the 12 sheets precedence.
The formula has become longest and when I must move or add of the columns in
the sheets precedence the formula need modified manually. It's possible to
insert simple macro that functions like vlookup that controls l' heading of
column of 13° the sheet and verifies in the twelve sheets precedence always
with the same heading of column if there are of the data to add relative to
the value of the cell to search? thanks.
John



Sheet1
cod val1 val2 val3 val4
A 0,03 0,04 0,05 0,06
B 0,04 0,05 0,06 0,07
C 0,04 0,06 0,07 0,08
D 0,05 0,06 0,08 0,09
E 0,05 0,06 0,08 0,10
F 0,05 0,07 0,09 0,11

sheet2
cod val1 val2 val3 val4
A 0,01 0,04 0,05 0,06
B 0,04 0,01 0,03 0,07
C 0,04 0,06 0,07 0,08
F 0,02 0,07 0,08 0,11

sheet3
cod val1 val2 val3 val4
A 0,03 0,04 0,05 0,06
B 0,04 0,05 0,06 0,07
C 0,04 0,06 0,07 0,08
D 0,04 0,06 0,08 0,07
F 0,05 0,07 0,09 0,11

........
........
........


Sheet 13
cod val1 val2 val3 val4
A =VLOOKUP(....)+=VLOOKUP(...)+VLOOKUP(...)+VLOOKUP( ...)+.................
B =VLOOKUP(....)+=VLOOKUP(...)+VLOOKUP(...)+VLOOKUP( ...)+.................
C =VLOOKUP(....)+=VLOOKUP(...)+VLOOKUP(...)+VLOOKUP( ...)+.................
D =VLOOKUP(....)+=VLOOKUP(...)+VLOOKUP(...)+VLOOKUP( ...)+.................
E =VLOOKUP(....)+=VLOOKUP(...)+VLOOKUP(...)+VLOOKUP( ...)+.................
F =VLOOKUP(....)+=VLOOKUP(...)+VLOOKUP(...)+VLOOKUP( ...)+.................

vlookup
 

I'm not quite sure I understand, but you may want to consider using a separate
column of cells for each =vlookup(). You could hide these columns and just show
the column that does the =sum(ofthosehiddencolumns)

As for the =vlookup() column changing when you add columns to the worksheets
with the tables, maybe it's time to replace the =vlookup() with a combination of
=index(match()) formula.

Match on the first row and match on the first column to find out what should be
returned.

Debra Dalgleish has lots of notes on =vlookup() and =index(match()) he
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))
and
http://contextures.com/xlFunctions02.html#Trouble

Take particular notice of example 2:
http://www.contextures.com/xlFunctio...ml#IndexMatch2

john wrote:

Hello to all,
I have the archives excel with 12 sheets with data
and a 13° for the totals.
I have used in 13° the sheet in several columns vlookup+ vlookup+ +vlookup+
.............. in order to add the data of the 12 sheets precedence.
The formula has become longest and when I must move or add of the columns in
the sheets precedence the formula need modified manually. It's possible to
insert simple macro that functions like vlookup that controls l' heading of
column of 13° the sheet and verifies in the twelve sheets precedence always
with the same heading of column if there are of the data to add relative to
the value of the cell to search? thanks.
John

Sheet1
cod val1 val2 val3 val4
A 0,03 0,04 0,05 0,06
B 0,04 0,05 0,06 0,07
C 0,04 0,06 0,07 0,08
D 0,05 0,06 0,08 0,09
E 0,05 0,06 0,08 0,10
F 0,05 0,07 0,09 0,11

sheet2
cod val1 val2 val3 val4
A 0,01 0,04 0,05 0,06
B 0,04 0,01 0,03 0,07
C 0,04 0,06 0,07 0,08
F 0,02 0,07 0,08 0,11

sheet3
cod val1 val2 val3 val4
A 0,03 0,04 0,05 0,06
B 0,04 0,05 0,06 0,07
C 0,04 0,06 0,07 0,08
D 0,04 0,06 0,08 0,07
F 0,05 0,07 0,09 0,11

.......
.......
.......

Sheet 13
cod val1 val2 val3 val4
A =VLOOKUP(....)+=VLOOKUP(...)+VLOOKUP(...)+VLOOKUP( ...)+.................
B =VLOOKUP(....)+=VLOOKUP(...)+VLOOKUP(...)+VLOOKUP( ...)+.................
C =VLOOKUP(....)+=VLOOKUP(...)+VLOOKUP(...)+VLOOKUP( ...)+.................
D =VLOOKUP(....)+=VLOOKUP(...)+VLOOKUP(...)+VLOOKUP( ...)+.................
E =VLOOKUP(....)+=VLOOKUP(...)+VLOOKUP(...)+VLOOKUP( ...)+.................
F =VLOOKUP(....)+=VLOOKUP(...)+VLOOKUP(...)+VLOOKUP( ...)+.................

--

Dave Peterson

vlookup
 

Hi
in sheet 13 (search cod and add data)
this is sample formula in 100 cells approximately

=IF(ISNA(VLOOKUP($C5;JAN;3;FALSE));"0";(VLOOKUP($C 5;JAN;3;FALSE)))+
IF(ISNA(VLOOKUP($C5;FEB;3;FALSE));"0";(VLOOKUP($C5 ;FEB;3;FALSE)))+
IF(ISNA(VLOOKUP($C5;MAR;3;FALSE));"0";(VLOOKUP($C5 ;MAR;3;FALSE)))+
IF(ISNA(VLOOKUP($C5;APR;3;FALSE));"0";(VLOOKUP($C5 ;APR;3;FALSE)))+
IF(ISNA(VLOOKUP($C5;MAY;3;FALSE));"0";(VLOOKUP($C5 ;MAY;3;FALSE)))+
IF(ISNA(VLOOKUP($C5;JUN;3;FALSE));"0";(VLOOKUP($C5 ;JUN;3;FALSE)))+
IF(ISNA(VLOOKUP($C5;JUL;3;FALSE));"0";(VLOOKUP($C5 ;JUL;3;FALSE)))+
IF(ISNA(VLOOKUP($C5;AGO;3;FALSE));"0";(VLOOKUP($C5 ;AGO;3;FALSE)))+
IF(ISNA(VLOOKUP($C5;SEP;3;FALSE));"0";(VLOOKUP($C5 ;SEP;3;FALSE)))+
IF(ISNA(VLOOKUP($C5;OCT;3;FALSE));"0";(VLOOKUP($C5 ;OCT;3;FALSE)))+
IF(ISNA(VLOOKUP($C5;NOV;3;FALSE));"0";(VLOOKUP($C5 ;NOV;3;FALSE)))+
IF(ISNA(VLOOKUP($C5;DEC;3;FALSE));"0";(VLOOKUP($C5 ;DEC;3;FALSE)))

It's too much long for maintenance
it would be better a macro

thanks

John

On Tue, 21 Oct 2008 14:51:59 -0500, Dave Peterson wrote:

I'm not quite sure I understand, but you may want to consider using a separate
column of cells for each =vlookup(). You could hide these columns and just show
the column that does the =sum(ofthosehiddencolumns)

As for the =vlookup() column changing when you add columns to the worksheets
with the tables, maybe it's time to replace the =vlookup() with a combination of
=index(match()) formula.

Match on the first row and match on the first column to find out what should be
returned.

Debra Dalgleish has lots of notes on =vlookup() and =index(match()) he
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))
and
http://contextures.com/xlFunctions02.html#Trouble

Take particular notice of example 2:
http://www.contextures.com/xlFunctio...ml#IndexMatch2

john wrote:

Hello to all,
I have the archives excel with 12 sheets with data
and a 13° for the totals.
I have used in 13° the sheet in several columns vlookup+ vlookup+ +vlookup+
.............. in order to add the data of the 12 sheets precedence.
The formula has become longest and when I must move or add of the columns in
the sheets precedence the formula need modified manually. It's possible to
insert simple macro that functions like vlookup that controls l' heading of
column of 13° the sheet and verifies in the twelve sheets precedence always
with the same heading of column if there are of the data to add relative to
the value of the cell to search? thanks.
John

Sheet1
cod val1 val2 val3 val4
A 0,03 0,04 0,05 0,06
B 0,04 0,05 0,06 0,07
C 0,04 0,06 0,07 0,08
D 0,05 0,06 0,08 0,09
E 0,05 0,06 0,08 0,10
F 0,05 0,07 0,09 0,11

sheet2
cod val1 val2 val3 val4
A 0,01 0,04 0,05 0,06
B 0,04 0,01 0,03 0,07
C 0,04 0,06 0,07 0,08
F 0,02 0,07 0,08 0,11

sheet3
cod val1 val2 val3 val4
A 0,03 0,04 0,05 0,06
B 0,04 0,05 0,06 0,07
C 0,04 0,06 0,07 0,08
D 0,04 0,06 0,08 0,07
F 0,05 0,07 0,09 0,11

.......
.......
.......

Sheet 13
cod val1 val2 val3 val4
A =VLOOKUP(....)+=VLOOKUP(...)+VLOOKUP(...)+VLOOKUP( ...)+.................
B =VLOOKUP(....)+=VLOOKUP(...)+VLOOKUP(...)+VLOOKUP( ...)+.................
C =VLOOKUP(....)+=VLOOKUP(...)+VLOOKUP(...)+VLOOKUP( ...)+.................
D =VLOOKUP(....)+=VLOOKUP(...)+VLOOKUP(...)+VLOOKUP( ...)+.................
E =VLOOKUP(....)+=VLOOKUP(...)+VLOOKUP(...)+VLOOKUP( ...)+.................
F =VLOOKUP(....)+=VLOOKUP(...)+VLOOKUP(...)+VLOOKUP( ...)+.................

--

Dave Peterson