What is wrong with this formula

[cherokee]

=MATCH(AM$13,INDIRECT('[3CH Position Directional Index.xlsx]Position 1'!"L"&SUM(AM$15:AM15)+1&":'[3CH Position Directional Index.xlsx]Position 1'!$L$15566")

 

[mrshl9898]

Does it work with the book open?

Pretty sure INDIRECT needs the book open to work

 

[barry houdini]

=MATCH(AM$13,INDIRECT('[3CH Position Directional Index.xlsx]Position 1'!"L"&SUM(AM$15:AM15)+1&":'[3CH Position Directional Index.xlsx]Position 1'!$L$15566")

Do you get an error, if so what error?

Your formula has more opening parentheses than closing parentheses

There's no 3rd argument for MATCH function - if you expect an exact match you need zero as third argument

I'd expect quotes right at the start of the INDIRECT function, i.e. to enclose the workbook and sheet name too

 

[FDibbins]

To confirm post #2 , INDIRECT only works on open files, not closed work books

 

[cherokee]

Does it work with the book open?

Pretty sure INDIRECT needs the book open to work

The book is open. The following works geat in the same book.

=MATCH(AP$13,INDIRECT("L"&SUM(AP$15:AP15)+1&":$L$15566"),0) I must put many calculatios in another book to keep Excel from not responing.

<tbody>
</tbody>

 

[cherokee]

I did not copy the entire formula. Here is the correct one

=MATCH(AM$13,INDIRECT('[3CH Position Directional Index]Position 1'!"L"&SUM(AM$15:AM15)+1&":'[3CH Position Directional Index]Position 1'!$L$15566"),0)

<tbody>
</tbody>

 

[mrshl9898]

There is a couple of issues here.

=INDIRECT("Sheet1!L"&SUM(AP$15:AP15)+1&":$L$15566")

Is working for me, note I have Sheet1!Lx:L15566 where you have Sheet1!Lx:Sheet1!L15566,(Sheet instead of position of course) as well as Barrys mention of the quotation marks.

 

[FDibbins]

Any time you refer to text in the INDIRECT formula - for instance a hard-coded file name, that text needs to be enclosed in "" the only time you don't need then (and would not use them) is if you are referring to data in a cell ref.
So if the file name is in a cell that you are referencing...
=MATCH(AM$13,INDIRECT("'["&A1&".xlsx]Position 1'!"L"&.........cell range
where A1 contains the text string 3CH Position Directional Index as the file name

On the other hand, if you hard-code the file name...
=MATCH(AM$13,INDIRECT("'[3CH Position Directional Index]Position 1'!cell-range"..........

 

[cherokee]

Nothing is working now. What bothers me now, is at one time I had it working. Computer crashed. I had backup but it was a day behind.