How to look up for a value in a workbook in the network toanother workbook in Excel 2010?

Hi guys,

I need to copy some information from one Workbook to another one, both Workbooks are in a network on diferent folders, let say:


On the Sheet "Registros" of the Registro_Correos_Oficiales.xlsm', a have a table named Table1, this table has 13 colums, (A to M)

 So, the value I need to find is "Administration" that in on column H, then once I find this value, I need to bring all the information in the NewFile.

I try this formula:

=VLOOKUP("Administración",'K:\Titular\Registro_Correos_Oficiales.xlsm'!Table1[[#Data],[#Totals],[NUM]:[TURNADO A]],'K:\Titular\[Registro_Correos_Oficiales.xlsm]Registros'!$A$2:$O$1000,FALSE)

But ovious is wrong, I hope you guys can help me, thank you in advance.
Who is Participating?
Ejgil HedegaardConnect With a Mentor Commented:
Macro is a solution, but it can be made without macros, using Match, Index, Indirect and links, see attached with a sheet named Mails.

I column P is a link to the values in column H in Registros.
Column O find the rownumbers for "Administración" in column P, by use of Match and Indirect. Looks down to row 100, expand if you need.
The Indirect function can not work across workbooks, unless both are open, so it can not directly look into the Registros file, that is the reason for column P.

The columns A to M finds the desired information from Registros, using the Index function and the row numbers found in column O.
I have made the table for 10 mails, expand to needed, columns A to O.

Move the Mails sheet to a new workbook, and save both files on the correct location, then the links will be correct.
Change the value "Administración" in the formulas in column O to the other departments.
Use replace, then you don't have to change the formulas.
Observe that formula O3 is different from O2, O3 can be copied down.
The columns O and P could be moved to another sheet, or hidden, so it is not visible.
Rob HensonFinance AnalystCommented:
The syntax of the VLOOKUP formula is:

=VLOOKUP(LookUpValue, LookupRange,Offset,LookUpType)

LookUp Value - The value that you want to find in the first column of LookupRange
LookUp Range - The data table in which you are searching
Offset - The column number from which you want to return data
LookUp Type - TRUE or FALSE, FALSE finds an exact match of LookUp Value; TRUE finds the closest match depending on sort order.

In your example the "Administracion" needs to change to the LookUp value for which you are finding details. The reference to Administracion would be in the Offset which would set the Offset to 8, column H being the 8th column of the data range starting in column A.

Rob H
Rob HensonFinance AnalystCommented:
I should have also said, the VLOOKUP formula will look for only one value in the column and will return only one value from the specified column.

Going by the reference to rows 1 to 1000, it looks like you are trying to return the whole column.

Rob H
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Harry LeeCommented:

Please make this more clear. Please correct me if I'm wrong.

From what I understand, you want to search Column H on Sheet Registros on File K:\Titular\Registro_Correos_Oficiales.xlsm. If the value sitting in Column H is Administracion, you want to copy the whole row onto a new sheet on a new file.

If that's the case, Vlookup is definitely not your answer.

I would say VBA is your best friend here. 2nd choice, using Index Match formula.

Can you please upload a sample file with dummy data for me to work on creating the VBA and Index Match sample for you?
elm9999Author Commented:
Thank you very much guys,

I think is better idea to make a VBA code as Harry says, let me explain what I need to do.

Theere is a co-worker who receives a lot of e-mails everyday and she has to register them on a Table of the workbook and select wich department the e-mail belongs to.

Now, on the other hand, I need to make a new file for each department, so when people open the new file they can see the emails belongs to their department.

For example When Administration department opens the new file they can see only those records belong to Administration and also The Status = "Pendiene".

The main Workbook is on a network:

\\Myserver\Intercambio\Titular\Registro_Correos_Oficiales.xlsm or


Attached is the main file.

Thank you again in advance.
elm9999Author Commented:
Thank you so much guys,

The option of hgholt works great and I'll use it to finish this job.

Harry LeeCommented:

Put the following code in a module in the personal macro workbook.

Open the daily file you referred to "K:\Titular\Registro_Correos_Oficiales.xlsm"

Then run the macro.

It will save a copy of the "K:\Titular\Registro_Correos_Oficiales.xlsm" in "K:\Tecnologias_informacion\" then remove all rows that H is not Administración.

This is kind of working the other around. Instead of searching for rows with H equal to Administración and copy to other workbook, this is making a copy of the workbook, and remove all rows that H is not equal to Administración.

Sub FilterListtoAdministracion()

Dim Datestamp As String, OrgFN As String, FPath As String, NewFFN As String, I As Integer
'Turn off screen update and auto calc to speed macro up
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'Configure where to save new files
FPath = "K:\Tecnologias_informacion\"

'Create New Filename
OrgFN = Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)
Datestamp = Format(Now(), "MMDDYYYY-HHMM")
NewFFN = FPath & OrgFN & " " & Datestamp & ".xlsm"

'Save file with new path and stamped name
Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:= _
        NewFFN, FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.DisplayAlerts = True

'Remove all rows that H is not Administración
For I = Cells(Rows.Count, 1).End(xlUp).Row - 1 To 2 Step -1
    If Cells(I, 8) <> "Administración" Then
        Range("A" & I).EntireRow.Delete
    End If

'Reenable screen update and auto calc
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Open in new window

Harry LeeCommented:

One big problem if using Index and Match, you will have to fill your new sheets with formulas which there are 2 big problems.

1) the file size is unnecessarily large
2) if you have lots of rows, the spreadsheet will run very slowly.

Using VBA does not increase your file size as much as formulas, and the result is a clean data without formulas for you to screw up in the future.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.