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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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.
Ejgil HedegaardCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.