INDIRECT Function Gives #VALUE! Error on Reopening

[Rob Bennett]

I'm running into an odd problem with Excel. I have a workbook that gets external data from a text file and puts it in column A of the "Raw Data" tab. I then use the following in another sheet to read the data line by line:

=IF(LEFT(INDIRECT("'Raw Data'!$A"&ROW()),4)="Name",1,0)

This works great until I close the workbook. When I close and reopen it, I get the #VALUE! error everywhere I use the INDIRECT function. If I put my cursor in the function bar and click out of it, all the INDIRECTs suddenly fix themselves. I'm building this for a client and would rather not explain the t they have to do this fix every time they open the file. Any guesses?

Thanks in advance.

[Rob Bennett]

Never mind - found the solution.

Apparently the INDIRECT function doesn't like the & concatenator on startup. I replaced the equation with this:

=IF(LEFT(INDIRECT(CONCAT("'Raw Data'!A",ROW())),4)="Name",1,0)

It now works just fine.

[FDibbins]

I have never had a problem using & in an INDIRECT function, in fact you need to use it, most of the time. There must be another reason. Are you by any chance referencing a closed WB?

[Rob Bennett]

Thanks, Ford.

I’ve never had a problem with using & either. I am referencing a different sheet in the same workbook, so it should work, but it failed every time I reopened the workbook. The only change I made was swapping & with CONCAT(), and now it works. I even simplified it:

=INDIRECT("'Raw Data'!A"&ROW()) — #VALUE! on reopen

=INDIRECT(CONCAT("'Raw Data'!A",ROW())) — works every time

Sounds like a bug to me....

[FDibbins]

hmmm strange. Any chance of a sample WB to test?