Auto populate

Kashmala Zaman

Hello,

I want to set up the auto-populate formula for the column "shipping & receiving" to be filled whenever column "pick up location" is filled.

Any help or advice is appreciated. Thanks

Kelly Moore

Hey Kashmala

The 2nd sheet is one solution option and doesn't affect the dropdown you have on the target sheet. The advantages of the 2nd sheet is the clear relationship between the locations and the owners - it makes it easy to update who the owner is.

If we went this route, we would use an INDEX/MATCH for the formula to put in your [Shipping & Receiving] column

=INDEX({2nd sheet Contact}, MATCH([Pickup Location]@row, {2nd sheet Location},0))

Since this is a cross referenced formula, you will have to build these cross reference ranges rather than just copy pasting the formula above. As a good practice, rename the generic smartsheet range number with the actual column name being referenced.

The other option is the nested IF. To update a name, one has to find it in the formula and update that text.

=IF(CONTAINS("Duneland", [Pickup Location]@row), "jim.buhring's real email address", IF(CONTAINS("La Porte A", [Pickup Location]@row),"marshall.warner's real email address", IF(CONTAINS("La Porte B", [Pickup Location]@row),"steve.starkeys real email address", IF([Pickup Location]@row="Lebanon", "rick.dravet's real email address", IF([Pickup Location]@row="GCLP", "tony.canzeroni's real email address", IF([Pickup Location]@row="Portage", "joseph.krajnek's real email address"))))))

This formula above can be copy pasted into your [Shipping & Receiving] column however the actual email addresses need to be inserted.

Kelly

Kelly Moore

Hello Kashmala

When you say you tried to apply the formula to another column, please describe your process. Specifically, were you using the edit reference link in the formula window?

Or copy paste the formula into a new cell and overwrite the name of the cross sheet reference name?

If yes, the problem is you're using the same cross reference link in both columns, but you're changing the name of range (but it's a name change only). If yes, go to the new formula and delete the range associated with the INDEX. Once you do that you will have the option in the formula window to Reference Another Sheet. Click the button to be taken to your sheet. Before leaving that sheet, use the good practice to change the name of the generic range number to reflect your referenced column.

The above is just my guess of what's going on with your formula. If I guessed wrong, please describe your process and screenshots always are helpful

Kelly

Kelly Moore

Hey @Kashmala Zaman

I'd be happy to help. I noticed that the person assigned is dependent upon the location selected. How large is the list of locations? I also noticed that La Porte A has two different people assigned to it. I will also need to understand why sometimes person A vs person B

The number of locations may influence the solution. Please let me know how many locations.

cheers,

Kelly

Kashmala Zaman

Thank you.

There are 9 locations, thank you for pointing out there was a mistake on the list I posted earlier.

here is the correct list.

Kelly Moore

Is the data above already in a second sheet?

Kashmala Zaman

No, this sheet I created to test some formulas I was finding on the internet ( I failed).

The original sheet where I want the formula to be set up has a drop-down at the moment.

Kelly Moore

Hey Kashmala

The 2nd sheet is one solution option and doesn't affect the dropdown you have on the target sheet. The advantages of the 2nd sheet is the clear relationship between the locations and the owners - it makes it easy to update who the owner is.

If we went this route, we would use an INDEX/MATCH for the formula to put in your [Shipping & Receiving] column

=INDEX({2nd sheet Contact}, MATCH([Pickup Location]@row, {2nd sheet Location},0))

Since this is a cross referenced formula, you will have to build these cross reference ranges rather than just copy pasting the formula above. As a good practice, rename the generic smartsheet range number with the actual column name being referenced.

The other option is the nested IF. To update a name, one has to find it in the formula and update that text.

=IF(CONTAINS("Duneland", [Pickup Location]@row), "jim.buhring's real email address", IF(CONTAINS("La Porte A", [Pickup Location]@row),"marshall.warner's real email address", IF(CONTAINS("La Porte B", [Pickup Location]@row),"steve.starkeys real email address", IF([Pickup Location]@row="Lebanon", "rick.dravet's real email address", IF([Pickup Location]@row="GCLP", "tony.canzeroni's real email address", IF([Pickup Location]@row="Portage", "joseph.krajnek's real email address"))))))

This formula above can be copy pasted into your [Shipping & Receiving] column however the actual email addresses need to be inserted.

Kelly

Kashmala Zaman

Thanks, how do I insert a formula in the drop-down contact cell? it's not letting me type.

Kelly Moore

Do you have the column properties restricted to Dropdown List only? Also, you are an admin or above on this sheet, correct?

To verify also, you are inserting the formula in a cell within the column, and not anywhere in the column property settings.

Kashmala Zaman

Yes, I am an admin, the dropdown was restricted.

=INDEX({2nd sheet Contact}, MATCH([Pickup Location]@row, {2nd sheet Location},0))

This worked , Thanks a lot :)

Kashmala Zaman

Hello,

I am trying to set up the same formula for the "ship to" & "CS Rep." column but it's applying the changes to the shipping & receiving column as well. I even tried creating the data for "ship to" & "CS Rep." in a separate sheet but still the same issue.

Please help.

Kelly Moore

Hello Kashmala

When you say you tried to apply the formula to another column, please describe your process. Specifically, were you using the edit reference link in the formula window?

Or copy paste the formula into a new cell and overwrite the name of the cross sheet reference name?

If yes, the problem is you're using the same cross reference link in both columns, but you're changing the name of range (but it's a name change only). If yes, go to the new formula and delete the range associated with the INDEX. Once you do that you will have the option in the formula window to Reference Another Sheet. Click the button to be taken to your sheet. Before leaving that sheet, use the good practice to change the name of the generic range number to reflect your referenced column.

The above is just my guess of what's going on with your formula. If I guessed wrong, please describe your process and screenshots always are helpful

Kelly

Kashmala Zaman

You are absolutely right. I corrected my mistake and now it works :) Thank you.

Kelly Moore

Glad we were able find the issue. Thanks for reaching out to the community

cheers,

Kelly