Equivalent to Excel Indirect function to dynamically change values in a formula

[cgoodman]

In Excel you can use an Indirect function to create strings to return a valid cell reference.

For example if in column A you have a list of sheet names e.g.

A1 = New York

A2 = Paris

A3 = London

Then you could create a formula which is Indirect(A1&"!B2") to reference the cell B2 in the New York tab.

An example of where I'd want to use this in Alteryx is to create a dynamic value based on a variable field.

For example based on this data:

I write [car make]+"_"+[car model] in the formula it will return the following

Volkswagen_Golf

Pontiac_Firebird

Audi_TT

Subaru_Outback etc.

However if I want it to be dynamic in an app or macro to allow the user to choose a suffix via a drop down this is not straight forward.

If I set up the tooling in this way:

What I get is the [#1] will bring in the field name and not the field value. So it would come through as Volkswagen_car model.

So my suggestion is that you have the ability to something like

[car make]+indirect("["+[#1]+"]") which would evaluate to [car make]+"_"+[car model] when car model is selected in the drop down.

The current workaround with help from @JonathanSherman is to use a dynamic select where [Name] = [#1] then join that onto the original dataset and use a dynamic rename create a new dynamic suffix field that can then be used.

Which while a nice design pattern, isn't as intuitive as indirect for those coming from a strong Excel background.

[cgoodman]

Having thought about it, potentially over complicating it due to the original way of wiring in the drop down. Using an action tool does allow this to change dynamically without the need for the dynamic select, join, dynamic rename.

However when wired in this configuration the interface pane cannot be used for debugging.

[YLYONG]

Hi All,

I have an excel worksheet where I used "INDIRECT" to obtain the results that I wanted.

Wanting to reach out the community here to how "INDIRECT" workflow works in Alteryx?

Thank you

[fmvizcaino]

I`m surprised this didn`t get enough votes! It would solve so many different use cases