How to automatically change column name based on user selection from drop down list?

[horusleo]

I will be using part of the training materials for  illustration purpose.

Attached is the main workflow (Exercise7-macro) and also the macro file (Macro_currency_con) that i am using.

After running the main workflow , i will get the image below as final output. I have no problem with the figures, The user will select either one of the currency and the computation will return the sales amount in that currency. However i am trying to get the column name (highlighted yellow) to change to whatever a user had selected in macro (either EURO, RUPEE or BIRD) instead of just a simple prefix/suffix to the column name. Is there anyway to do it without manually changing it such as using the Select tool?

Reason being my actual data has multiple options for user to select (more than 100) so its not really viable to hardcode/manually change it.  

I had tried to use Dynami Rename but cant seems to get it done.

Please advise thanks!!

 

[Podoseb0]

Not sure if there's a way to pass the [name] value from the dropdown, since it automatically gets the correspondent value in the [value] field.

One workaround you could try is to change the values of [value] field by concatenating it with the [name] field: [name]+[value]. In this case you'll have all the information you need, name and value of the currency in one field, which you can then separate.

See if the attached is somehow useful.

[horusleo]

Thanks for the reply and solution. i had tried your solution , it does works to a certain extend if i just run it outright and output it to an excel file.

However when i try to repackage it into a Standard Macro, it doesnt work.

Is there anyway to make it works within a Macro? 

Attached the said workflow.

[acarter881 · Accepted solution]

Hello @horusleo. I believe I figured it out. It is tough. I was able to do it by concatenating both the name and value and putting that into the value field then splitting the values, as needed, using REGEX_Replace. Please see the attached and the spoiler. Please let me know if this helps.

Spoiler

[horusleo]

hii @acarter881. Thanks for the input. Your solution actually works! Although the source excel file is slightly modified to suit this purpose, that is a pretty minimal effort as for my case my actual exchange rate files are not too crazyly huge to do that. I understoof that sometimes getaround have to be done! This could be as close as i get from attempting to fully automate the process.

Thanks appreciate much!

[Thomas_Wells_2dot0]

Hi @horusleo 

I sort of re-tooled your macro and turned this into an Analytic App. See attached.

Now when the user runs the Analytic App, they will select a currency based on the values in your Exchange rate table. I used a text input for the exrate data, but you could use an Excel file (you said your data has over 100 values). I like this option because the selection is based on the data, so it doesn't have to be hard coded.

Hope this helps. Thanks