| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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( ...)+................. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
| VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
| using a vlookup to enter text into rows beneath the vlookup cell | Excel Programming | |||
| Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Hybrid Mode
