Return column number of matching cell in external range

[Nazgûl]

Cell A1 in open_workbook_1 contains a text string.

The same text string will be found somewhere in closed_workbook_1
within the cell range A1:Z100

How can I return to cell A2 in open_workbook_1, the column number of
the cell in closed_workbook_2 which contains the text string?

Thanks.

 

[Alan Beban]

Cell A1 in open_workbook_1 contains a text string.

The same text string will be found somewhere in closed_workbook_1
within the cell range A1:Z100

How can I return to cell A2 in open_workbook_1, the column number of
the cell in closed_workbook_2 which contains the text string?

Thanks.

Assuming that the text string in the closed workbook is in the sheet
named "Sheet1", then if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your open workbook:

in cell A2 of the open workbook enter

=COLUMN(INDIRECT(A3))

and in cell A3 enter

=ArrayMatch(A1,[pathname to closed_workbook_2]Sheet1!A1:Z100,"A")

Alan Beban

 

[Nazgûl]

Cell A1 in open_workbook_1 contains a text string.

The same text string will be found somewhere in closed_workbook_1
within the cell range A1:Z100

How can I return to cell A2 in open_workbook_1, the column number of
the cell in closed_workbook_2 which contains the text string?

Thanks.

Assuming that the text string in the closed workbook is in the sheet
named "Sheet1", then if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your open workbook:

in cell A2 of the open workbook enter

=COLUMN(INDIRECT(A3))

and in cell A3 enter

=ArrayMatch(A1,[pathname to closed_workbook_2]Sheet1!A1:Z100,"A")

Alan Beban

Thanks. It doesn't seem to work with the "A" parameter if
closed_workbook_2 is actually closed - I get a "The lookup array is
not a range: the address parameter must be omitted" error (works fine
with the external workbook open). However, with leaving off the "A" I
can figure out how to get it to work.

I was hoping that it would be possible without any add-in functions
(it gets a little complicated making sure people remember to find and
add the module to new worksheets in order to get the ArrayMatch
formula). But I guess that's not possible??

 

[Domenic]

Try the following array formula that needs to be confirmed wit
CONTROL+SHIFT+ENTER...

=MIN(IF([closed_workbook_1.xls]Sheet1!$A$1:$Z$100=A1,COLUMN([closed_workbook_1.xls]Sheet1!$A$1:$Z$100)))

...where A1 is your lookup value in your open workbok.

Hope this helps!