Match and return across two workbooks

[Defaced]

I'm trying to figure out if what I want to do in my head will actually work and I am coming up short with VLOOKUps, Index/Match with Indirect and I'm at a loss.

I am simple trying to lookup what is in cell F1 on a sheet in one workbook across all tabs in another workbook (tab name is the same as F1) once it finds that tab it will return what is in cell M11. If it's not possible to lookup by tab name, the text that is in cell F1 is the same in both workbooks.

So if F1 in Last Name First Name, I want it to search the other workbook for that and return what's in cell M11. Is this possible?

 

[mumps]

I think you will need a macro to do what you want. Are you saying that you want to search another workbook for a tab which is named based on the value of cell F11? When that sheet is found, you want to return the value of M11? If this is correct, what is the name of the sheet which contains the search string in F11? What is the name of the other workbook including the extension (xlsx, xlsx, xlsm)? What is the name of the sheet and cell where you want to return the value from M11?

 

[JonXL]

I'm trying to figure out if what I want to do in my head will actually work and I am coming up short with VLOOKUps, Index/Match with Indirect and I'm at a loss.

I am simple trying to lookup what is in cell F1 on a sheet in one workbook across all tabs in another workbook (tab name is the same as F1) once it finds that tab it will return what is in cell M11. If it's not possible to lookup by tab name, the text that is in cell F1 is the same in both workbooks.

So if F1 in Last Name First Name, I want it to search the other workbook for that and return what's in cell M11. Is this possible?

You should be able to pull this off with just the INDIRECT function.

You will need to specify the workbook as well as the sheet in your formula, but try this (assuming you set the workbook name in cell E1):

=INDIRECT("["&E1&"]'"&F1&"'!$M$11")

 

[Defaced]

The sheet I am trying to pull the data into we'll call Workbook A and in cell F8 I want to put in a formula that will look at cell F1 (value is Person A) and use that to look across Workbook B (.xlsx file) and either look at the tabs and find Person A or look over all the sheets and find Person A in cell F1. Once it finds that I want to pull over what's in cell M11 to Workbook A and place in cell F8.

 

[mumps]

A formula might be the better way to go. Place this formula in cell F8: =INDIRECT("'[Sample.xlsx]"&F11&"'!M11") Change the workbook name to suit your needs.

 

[Defaced]

Get a #REF ! with that formula. I did a simple test with it, in Workbook A I put Name in A1, in Workbook B I put Name in A1 and 1234 in B1. In Workbook A in cell A4 I put =INDIRECT("'[Workbook B.xlsx]"&A1&"'!B1") It won't bring the 1234 into Workbook A

 

[mumps]

In Workbook A, cell A1 should have the sheet name and a sheet with that name should exist in Workbook B with "1234" in cell B1.

 

[Defaced]

Renamed the sheet in Workbook B to Name to match what's in Cell A1 in Workbook A and still #REF !

 

[mumps]

I tried it on dummy data and it worked properly. Perhaps you could upload a copy of your 2 files to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[JonXL]

What version of Excel are you using?

mumps's formula should work (I put the ' in the wrong place in mine).

However, I am also seeing a #REF! error in 2007. I'm going to test this on 2016 when I load up that computer later.