Formula "INDIRECT" does not work

[deri_b]

Dear all, hoping you can help me with my question - first time I am asking anything here, as I usually already find answers in previous posts...

I am using an "INDIRECT" formula to retrieve data from a different sheet.
The formula is as follows:
=+INDIRECT("'"&$C16&" "&$E16&"'!"&K$13)

C16 and E16 together yield the name of the sheet referenced. K13 yields the cell that needs to be retrieved.
There has neither been a sheet name change, nor is the cell in the sheet empty/ the wrong cell. The formula used to work on my colleagues Sharepoint just last week He has left the company and I have taken over his tasks. Therefore I copied the file to my Sharepoint. Then, today, it stopped working.

When I am in the sheet C16&E16, the cell referenced to as "K13" has the number value 12. It is formatted as "General", same as the cell with the "INDIRECT" formula. The sheet calculation is set to automatic.
When I use a direct link to the sheet and its cell, the value we is returned.
When I click into my direct formula and calculate the part after the equal-sign using F9, I receive the value 12.
However, when I leave the formula in tact and hit enter, it returns the value 0.

I am at a complete loss why. Any ideas, tips, incantations, or suggestions for office-friendly swear words are highly appreciated.


Best,
Deri

 

[Alex Blakenburg]

What happens if you do this:

=INDIRECT("'"&$C16&" "&$E16&"'!K$13")

 

[deri_b]

What happens if you do this:

=INDIRECT("'"&$C16&" "&$E16&"'!K$13")

Still yields 0. In your version, it also yields zero if I try to calculate manually within the cell using F9.

 

[Fluff]

Hi & welcome to MrExcel.
Check that you don't have any circular references.

 

[deri_b]

Hi & welcome to MrExcel.
Check that you don't have any circular references.

Thanks, Fluff. No circular references in the whole sheet.

 

[Alex Blakenburg]

Just to be clear. When you look at the bottom left corner on the status bar it doesn't say Circular reference there, right ?

If you select just this part and hit F9. What do you see ?

"'"&$C16&" "&$E16&"'!K$13"

If you put that value in a cell with a "=" at the front and drop the quote marks, does it look right.

 

[deri_b]

OK so the solution seems to have been that my colleague used Excel in German while use it in English. I reset my language to German and now it works. No idea why.