Helper Column help needed to use Data Shuttle

ycox

Hello,

I need assistance creating a helper column for the following scenario (bare with me).

I have a drop-down list in one column called Select Master Course Name which includes (Master Course Name, ID#).

I have created two helper columns to separate the Master Course Name and Master Course Code into individual columns so they can work properly in reports.

Master Course Code Formula - =RIGHT([Select Master Course Name]@row, LEN([Select Master Course Name]@row) - FIND(",", [Select Master Course Name]@row))

Master Course Name Formula - =IFERROR(LEFT([Select Master Course Name]@row, FIND(",", [Select Master Course Name]@row) - 1), "")

The issue that I'm running into now is because of the Formula in both of these helper columns, I can't update the drop-down list using Data Shuttle. The target file can not have formulas for mapping. What I would like to do is create another helper column that does what the Master Course Name column is currently doing and create an additional helper column to fill in the Master Course Name column so that it remains as a value so the Data Shuttle will work correctly for mapping.

Not an expert at this, but I really appreciate your insight and assistance. I've tried everything

Andrée Starå

Hi @ycox

I hope you're well and safe!

If I understand correctly, you could copy the rows to a helper sheet instead and use that for Data Shuttle.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

ycox

Thanks, Andree,

I went another route that seems to be working. I removed the ID # from the drop-down list, which allowed me to remove the formula from the course name column, and it is working correctly.

Thanks for your help

Andrée Starå

@ycox

Excellent!

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

ycox

Hello, @Andrée Starå. Can I ask a follow-up question from the same sheet? I would like to auto-populate information from a reference sheet into this sheet when a checkbox is marked. I created a formula using an IF Statement and Index Match, but it returns as Unparseable. I believe it's because the Index Match Column is a checkbox or dropdown list. I'm using a helper column for the formula.

=IF([No Change]@row, (INDEX({Master CE CMP Reference Sheet Range 5}, MATCH([Master Course Code]@row, {Master Course Code Column}, 0))= 1), 0)