When outputting complex-ish dynamic formulas to Excel am getting the #NAME? error

[Trevor]

Example of a desired output:

=IFS(AN2<>"",AN2,AD2>0,AD2,Z2>0,Z2,Z2=0,0)

Alteryx rendering:

'=IFS(AN'+tostring([Count])+'<>"",AN'+tostring([Count])+',AD'+tostring([Count])+'>0,AD'+tostring([Count])+',Z'+tostring([Count])+'>0,Z'+tostring([Count])+',Z'+tostring([Count])+'=0,0)'

([Count]  dynamically inserts the relevant row #.)

The desired outcome does get produced.

This would be found in the expected cell, if one were to look in it: =IFS(AN2<>"",AN2,AD2>0,AD2,Z2>0,Z2,Z2=0,0)

However a #NAME? error displays nonetheless, when one opens up the XLSX.

Until one selects the cell, simply clicks inside the formula bar, and simply hits enter.

Do that and the formula springs into dynamic life, w/o having to be altered in anyway (there not being a syntactical error).

But one doesn't want to have to futz w/ the output, if it can be helped.

So my workaround is to break the formula up into smaller chunks and insert them into a conditional if/then/elseif statement, outputting a different short and simple dynamic formula depending on this or that condition in other columns.

Like so:

if !isnull([future_retail]) then '=IF(AN'+tostring([Count])+'<>"",AN'+tostring([Count])+',AD'+tostring([Count])+')'
elseif !isnull([current_retail]) then '=IF(AN'+tostring([Count])+'<>"",AN'+tostring([Count])+',Z'+tostring([Count])+')'
else '=IF(AN'+tostring([Count])+'<>"",AN'+tostring([Count])+',0)'
endif

That works.

That averts having to manually activate the formula in Excel, after the fact.

It outputs something like one of three simpler formulas depending:

=IF(AN2<>"",AN2, AD2)

=IF(AN2<>"",AN2, Z2)

=IF(AN2<>"",AN2,0)

But is their a simpler way to output complex-ish dynamic Excel formulas, w/o changing the way one would input them when working directly in Excel?  Something to do w/ formatting, or data type?

And is it the size/complexity of the formula that's generating the irksomeness?  Or are there some Excel functions - IFS for example - for which the translation isn't going to be super smooth, for some reason?

Thanks!

-Trevor

[kathleenmonks]

Hi @Trevor would you be able to share your excel file so we can see exactly what is going on? 

As a first thought, this question on the excel forum might help regarding the way the cell is formatted - https://answers.microsoft.com/en-us/msoffice/forum/all/formula-is-valid-but-doesnt-work-until-cell-i...

[Trevor]

Thanks for the prompt response. 

I've attached a simplified flow that illustrates the problem.

Same data type chosen for both the good (workaround_output) and not as good (desired_output) formula.

At a glance, it seems like both would have been affected the same way, if the formatting issues discussed in the Excel forum link were at play.

Appreciate the help.

[kathleenmonks · Accepted solution]

Hi @Trevor thanks for sharing the workflow! I found another community discussion where there are a few workarounds. Here are some options:

1. output as a csv instead of xlsx from Alteryx - then when it is opened in Excel it will create the proper "desired_output" column 
2. if you want to stick with xlsx, you can highlight the column in Excel and click Data -> text to columns -> Finish and it will fix all the values 
3. it appears that someone in the discussion I linked above created a macro that will do step 2 automatically but I have not tested this

I tested options 1 and 2 with the workflow you provided and they both worked. Let me know if that helps solve your problem. If so, would you mark this as a solution? Thanks!

[Trevor]

Thank you