Using Excel to pass logic into Formula tool

Question

I have a workbook that has two tabs: DarthVadar and PrincessLeia.  In each tab, I have two columns, FieldName and Value.  See example below.  After the user selects the tab of choice (see workflow 1), I would like to update the values of the formula tool with the values within the worksheet.  The workflow that allows the user to select a specific tab works but I need guidance on how to update the formula tool with the values for the correct field names.

FieldNameValueNameAnakinRoleEVPAge45CountyJohnsonStateTXPostal Code76050Value1100Value2200Value3300Description 1ADescription 2BDescription 3C

Step 1: Allow user to select a specific tab

Step 2: Update the formula tools values to be based on the values from the selected tab.

Step 1  - Tab Selection.PNG

Step 2  - Update Formula Tool.PNG

BS_THE_ANALYST · Accepted Answer

If you're trying to get columns with a value in them driven from the sheet the user picks (in your scenario), you could just crosstab it after the dynamic input. This will swing the selected sheet's rows into column and value pairs.

This will make the formulas redundant as you'll already have the columns with the values populated in them (based on user selection).

KGT · Accepted Answer

I agree with @BS_THE_ANALYST, after the selected sheet is brought in, just cross tab it. You may need some renames to deal with spaces/punctuation on the cross-tab but that can be achieved simply by creating another column that is NewName=ReplaceChar([FieldName]," ","_") and then use [FieldName] & [NewName] in a Dynamic Rename.

The other thing I should point out here though is that the crosstab leading to the dropdown in your first screenshot will not be dynamic. As you are running the app in testing, the metadata for the output of the crosstab is being stored to configure the dropdown. When you run it as an app with new data, you will find the dropdown will still be populated with your testing data. When an app/macro executes, the interface is configured before any of the workflow is run, so the dropdown will be configured before the cross-tab executes.