Data Uploader - if value exists on input file, need it to update status field on source sheet

Christina Reid

We have tracking sheet that we use to track client accounts that are transferring. I want to take a different file that shows accounts that have confirmed been transfer and use Data Uploader to have the information on the input sheet either map to or change certain cells.

I have the mapping down - but I can't figure out the formula that says "If the account number listed on the source sheet is also found on the input sheet, update the Status column". In a perfect world what I really need it to do is "If the account number listed on the source sheet is also found on the input sheet, update the Status column with EITHER 'Complete, Waiting for Download' if the Account Balance is not available(less than .01) OR update the Status with 'Complete' if the Account Balance is available (greater than .01)."

The columns on my source sheet are titled Status, Account Number and Account Balance

The column on my input sheet is titled Account Number.

Genevieve P.

Hi Christina,

My apologies, you are totally right! You can use an expression to input the 1 in that helper checkbox column instead of adjusting your source sheet.

You are also correct in how this would be set up: in the data uploader workflow in the Mapping window, in the Input Column/Expression left side of the window, choose the option "New Expression" from the bottom of the drop-down list to Map to the column DL?

Then have this expression simply be =1

This will check the box in Smartsheet for the rows that were updated.

Formulas in the sheet itself will be a bit different, and I'm happy to help you with this.

Instead of this, =IF([Account Number]@row=NOTBLANK,"Yes",""), try this:

=IF([Account Number]@row <> "", "Yes", "")

<> means Not and "" means Blank so it's essentially the exact same as what you had written above. However, based on your description it sounds like there will always be an account number, but what you're looking for is if there was a match with the Data Uploader and an updated Balance, is that correct?

If so, you can adjust the mapping to include checking the box, as above, then use my previously suggested formula:

=IF(AND(ISBLANK([Account Balance]@row), [DL?]@row = 1), "Complete, Waiting for Download", IF([DL?]@row = 1, "Complete", "Not Complete"))

This will then tell you if there was a match but the balance is blank, or otherwise, there was a match and it's complete, or finally if there was no checkbox then that row is not complete.

Christina Reid

You are awesome! That totally worked and I really appreciate it!!!

Genevieve P.

Hi Christina,

You could set it up like this... "If the Account Number in the source sheet matches the Account Number in Smartsheet, update the Account Balance column and state that there is a match". Then in the Smartsheet sheet you can build a formula that looks at the account balance and reflects the different statuses that you would like, but only if there's a match (separate from Data Uploader).

To do so, you would need to add in a helper column in both sheets that essentially is just looking to confirm if the Account Number is in both. You could have this be a simple checkbox column in Smartsheet, and input the number 1 in every row in the other sheet, when there is new data. This will check the box in Smartsheet when you run the Data Uploader workflow, if the Account Number has a match.

You'll want to have all four columns in your Smartsheet sheet before setting up the Uploader workflow.

Your Unique Identifier column will be the Account Number column, and you'll map this, the Account Balance, and the matching checkbox column to their counter-parts. Make sure that you've selected "Update Existing Rows" in the Target section of the workflow, as well.

Once you have the workflow running as expected, this is the formula you can use in your Status Column in Smartsheet:

=IF(AND(ISBLANK([Account Balance]@row), [Matching Column]@row = 1), "Complete, Waiting for Download", IF([Matching Column]@row = 1, "Complete", "Not Complete"))

This will first check to see if the Account Balance is blank, even though there is a match - if so, then it will return "Complete, Waiting for Download". If the Account Balance is not blank, and there's a match, then it will return "Complete". Otherwise, if there is no match, the status will say "Not Complete".

Let me know if you think this will work for you!

Here are some Help Center articles I used:

• Data Uploader
• Checkbox in Formulas
• IF Function
• ISBLANK Function
• @row Function

Cheers,

Genevieve

Christina Reid

Ok, I'm struggling on:

"To do so, you would need to add in a helper column in both sheets that essentially is just looking to confirm if the Account Number is in both. You could have this be a simple checkbox column in Smartsheet, and input the number 1 in every row in the other sheet, when there is new data. This will check the box in Smartsheet when you run the Data Uploader workflow, if the Account Number has a match."

I setup a column on my SmartSheet that I titled DL? and setup as a checkbox. I'm not sure what else to do? I'd prefer to not have to make any changes to the sheet that I'm dropping in for upload as it defeats the purpose of the upload feature to mark these things as new. Is there an expression that I need to setup and map to the DL? in Data Uploader. At one point I did have the formula working with the checkbox and I thought the expression was something as simple as =1 (this was the formula as it was setup by my SmartSheet implementation specialist).

When I couldn't figure it out I change the column to text and I thought I had it when I entered a formula of =IF([Account Number]@row=NOTBLANK,"Yes","") as my source sheet showed updates only to the rows that had account numbers matching on both sheets, but they came back as #unparseable. I tried ISBLANK and MATCH with the same results.

Any clearer instructions on that particular piece would be helpful, as you can tell it's the peice of the puzzle I need to make some of the other formulas work.

Genevieve P.

Hi Christina,

My apologies, you are totally right! You can use an expression to input the 1 in that helper checkbox column instead of adjusting your source sheet.

You are also correct in how this would be set up: in the data uploader workflow in the Mapping window, in the Input Column/Expression left side of the window, choose the option "New Expression" from the bottom of the drop-down list to Map to the column DL?

Then have this expression simply be =1

This will check the box in Smartsheet for the rows that were updated.

Formulas in the sheet itself will be a bit different, and I'm happy to help you with this.

Instead of this, =IF([Account Number]@row=NOTBLANK,"Yes",""), try this:

=IF([Account Number]@row <> "", "Yes", "")

<> means Not and "" means Blank so it's essentially the exact same as what you had written above. However, based on your description it sounds like there will always be an account number, but what you're looking for is if there was a match with the Data Uploader and an updated Balance, is that correct?

If so, you can adjust the mapping to include checking the box, as above, then use my previously suggested formula:

=IF(AND(ISBLANK([Account Balance]@row), [DL?]@row = 1), "Complete, Waiting for Download", IF([DL?]@row = 1, "Complete", "Not Complete"))

This will then tell you if there was a match but the balance is blank, or otherwise, there was a match and it's complete, or finally if there was no checkbox then that row is not complete.

Christina Reid

You are awesome! That totally worked and I really appreciate it!!!

Genevieve P.

I'm so glad that worked for you! Thanks for letting me know 🙂