vlookup

[john]

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(...)+.................

 

[ShaneDevenshire]

Hi,

Could you post a sample of your current formulas, so it would be clearer
what you want.

 

[john]

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($C5;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

 

[ShaneDevenshire]

Hi,

Nice formula!

Here is a little VBA function you might use

Function MyLookup(Item As String, Months As Range)
Dim cell As Range
Dim x As Double
For Each cell In Range("months")
x = x + WorksheetFunction.VLookup(Item, Range(cell), 3, 0)
Next cell
MyLookup = x
End Function

In an area of your spreadsheet enter the list of you month range names.
Name this range Months. To use this formula enter it as
=MyLookup(A5,Months)
where A5 is the item you want to lookup on each sheet and Months is the
range name of the list of months.


If this helps, please click the Yes button.

 

[john]

Hi
ok, very good for a column,
but for the other columns I must change reference index
vlookup(3,4,5,6,7,8,9.........)

Thank
John