Forum Discussion

AGambus's avatar
AGambus
Copper Contributor
May 18, 2022

Sumar.si.conjunto

Hola, alguien sabe si funciona usar sumar.si.conjunto en varias hojas a la vez? Lo he probado pero me da error. Gracias Hi, does somebody know how to use sum.if.conjunto in several worksheets at t...
excel
Formulas and Functions
AGambus's avatar
AGambus
Copper Contributor
May 19, 2022

 Thanks a lot. HansVogelaar 

Have another question, would appreciate if you could help.

I have two files, first is origin, second is destination.

Origin file has several sheets with same content structure but different nº and data

nºXYZ
603135589

 

Destination file has a big table with all nº's in a column followed by X, Y, Z columns (empty cells). Is there any kind of VLOOKUP combination formula that I could use to retrieve all data at once?

 

Thanks in advance.

Antonio

HansVogelaar's avatar
HansVogelaar
MVP
May 19, 2022

AGambus 

In the destination workbook, create a list of the sheets in the origin workbook and name this range Sheets, as before.

If you have Microsoft 365 or Office 2021, enter the following formula in B2 of the sheet in the destination workbook:

 

=VLOOKUP($A2, INDIRECT("'[Origin.xlsx]"&INDEX(Sheets, MATCH(1, --(COUNTIF(INDIRECT("'[Origin.xlsx]"& Sheets &"'!A2:D1000"), $A2)>0), 0)) &"'!A2:D1000"), {2,3,4}, FALSE)

 

In Spanish:

 

=BUSCARV($A2; INDIRECTO("'[Origin.xlsx]"&INDICE(Sheets; COINCIDIR(1; --(CONTAR.SI(INDIRECTO("'[Origin.xlsx]"& Sheets &"'!A2:D1000"); $A2)>0); 0)) &"'!A2:D1000"); {2\3\4}; FALSO)

 

where Origin.xlsx is the name of the origin workbook (it should be open in Excel too).

Fill down.

 

If you have an older version, select B2:B2, enter the formula mentioned above and confirm it with Ctrl+Shift+Enter, then fill down.