INDIRECT returns #VALUE! error

[amdis]

Hi all,

So this is a problem I'm having with a 20+ tab model that I built out last year. The model works in a way that it needs to be able to expand and I've set it up so that you can just copy a tab, change the name and some details and it automatically pulls the relevant data from the previous tab. The problem I'm having now is that while this works fine on my PC, two of my analysts are having issues where whenever they open the file they're just bombarded with #VALUE errors. I've isolated the incident to this formula as the second I copy/pasted the cell contents as values when working on their PC the errors resolved themselves.

Here's the formula in question, it is dragged down and spans about 200+ rows:

=+IF(OR(INDIRECT(""&TEXT(EOMONTH($G$97, -1), "MMM")&"!E"&ROW(E107)&":F"&ROW(F107))=""),INDIRECT(""&TEXT(EOMONTH($G$97, -1), "MMM")&"!B"&ROW(E107)),"Empty")

I built this a while ago and now I can't even remember why I used that OR function but removing it doesn't seem to fix anything. Any ideas?

 

[Alex Blakenburg]

The formula shouldn't behave any differently on different machines. To get a #Value result you would have to put a text value into G97.

 

[amdis]

Yea I've checked and G97 definitely isn't a text value, it's an actual date. This thing is baffling, I've opened the file on my old personal laptop and am seeing the #value errors there too. Like I said in the original post, the second I remove the indirect formula and just hard code the errors resolve so it's definitely that formula.

 

[Alex Blakenburg]

Show me your hard-coded formula.

 

[Alex Blakenburg]

When you built the formula were you already on MS 365 ?
Is there any chance it works on MS365 machines and fails on those using earlier versions ?

 

[amdis]

Sorry about the slow replies, been a bit of a busy weekend. Thanks for your persistence in helping me. As for your first question, I used the wrong words. It's not so much a hard coded formula but it's just me hard coding the values. That's how I figured out it was the INDIRECT() formula that was the issue.

When you built the formula were you already on MS 365 ?

Yes, my analysts are on Office 2016 as we wait to get them onto a new laptop but I figured this is an issue I need to fix anyway as our auditors also use Office 2016 and haven't migrated to 365 yet. What I've found in my last few days of poking around is the formula works fine on my end but when I open it on my old personal laptop that is running Office 2016 the formulas all show up as CSE array formulas for some reason. If I press F2 and then enter to make them a normal formula instead of an array, the #VALUE error resolves itself. I'm guessing INDIRECT has issues accepting an array result and that's causing the problem here.

Not quite sure how to fix this moving forward.

 

[Alex Blakenburg]

I'm guessing INDIRECT has issues accepting an array result and that's causing the problem here.

I don't have an old version of Excel to determine whether INDEX is actually causing the issue or if its pre-MS365 problem.
When you replaced indirect in your formula and did you include the E107:F107 in the OR expression and did it work on the 2016 Excel ?

Try this to see if it does the same thing and works on all machines, I got rid of relying on an Array inside the OR and made it an explicit OR expression.

=IF(OR(INDIRECT(""&TEXT(EOMONTH($G$97, -1), "MMM")&"!E"&ROW(E107))="",INDIRECT(""&TEXT(EOMONTH($G$97, -1), "MMM")&"!F"&ROW(F107))=""),INDIRECT(""&TEXT(EOMONTH($G$97, -1), "MMM")&"!B"&ROW(E107)),"Empty")

 

[amdis]

Just tried your solution, same thing as the other. Both versions of the formula "work," so to speak. The issue continues to be that Excel keeps taking my version of the formula and turning it into a CSE array formula when opened on older machines. I saw some online conversations about prepending "@" to the formulas to avoid this happening but that doesn't seem to work either.