Matching a text reference in separate Excel sheets

I have been going crazy trying to write a statement that will allow me to find a text reference that is common to both spreadsheets but in different column, row numbers. Once I have found the cell I want to offset the column position by 17 and then copy the contents of that final cell back into the original spreadsheet.

What I tried was:
In spreadsheet1, at position cell R10 I typed the following ... =match("text in spreadsheet1", spreadsheet2!$E:E,0) 'this returned a number 14, saying that the text in spreadsheet1" was found in spreadsheet 2 at position cell E14.

so then I tried
=concatenate("U",R10) 'this then gave U14 as the new position of what I wanted the contents copied back into spreadsheet 1.

This is where I got stuck. How do I copy the contents of the cell U14 in spreadsheet 2 back into spreadsheet 1 at cell R10 (ie where I started from)?

Any help would be appreciated?

Cheers

Hi prssmzk,

Try below formula in any other cell except R10 to avoid circular reference.

Formula:

Please Login or Register  to view this content.

Regards,
DILIPandey
<click on below * if this helps>

Before checking even the sheets you attached.. I have to say that "Indirect" function does'nt work on closed workbooks. So if you applied indirect function in sheet1.xlsx to get data from sheet2.xlsx (and if sheet2.xlsx is closed at the moment), you'll not get results fetched out

Why I suggested Indirect function in my example is because you have used the term "sheet" in your query and sheet(s) are there in a workbook, so indirect function will work in a workbook and you can fetch data from any sheet, within workbook only.

Regards,
DILIPandey
<click on below * if this helps>

Alternatively, you can use below formula in M3 of sheet 1 and drag towards right :-

Formula:

Please Login or Register  to view this content.

Regards,
DILIPandey
<click on below * if this helps>

Okay... see the attached zip file where I have included both the workbooks

prssmzk.zip

Regards,
DILIPandey
<click on below * if this helps>

To get those curly brackets, enter formula using key combination ctrl shift enter and that's called array

cheers

Regards,
DILIPandey
<click on below * if this helps>