I have a very strange Excel problem that I cannot wrap my head around:

I have the following formula: =IFERROR(LEFT(INDIRECT(ADDRESS(INT(((ROW()))/4) 2,3,,,"Sheet1")),(FIND(" ",INDIRECT(ADDRESS(INT(((ROW()))/4) 2,3,,,"Sheet1")),1)-1)),"")

which essentially goes through a different sheet and looks for string entries including a space and then returns only the first word and outputs the same results for 4 consecutive rows.

Typing this formula into Excel and executing it manually works. Dragging the formula down a couple rows also works.

When I now save, close and reopen the workbook, the cells are empty. The formula is still present but now it is in array format {} and the results do not show. If I now click on the cells with my mouse and execute them again by pressing enter it calculates correctly. See picture below.

Cells only display result after manual execution

So I know the formula still works. Copying the formula, deleting the entire array and pasting it and dragging it down results again in a working formula.

Does anyone what is causing this strange behavior and how I can fix it?

Many thanks in advance and best regards.

CodePudding user response：

Make Sure your Workbook Calculation is set to Automatic.

Please check once here - Go to File > Option > Select 'Formulas' on Left > Check under 'Calculation Options' if Workbook Calculation is set to Automatic. If not, make it Automatic and give it a try.

CodePudding user response：

Suggest you rewrite your formula to use OFFSET rather than INDIRECT(ADDRESS. Also, if you're using WEBSERVICE anywhere it is not affected by automatic calculation.