Auto populate
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
Best Answers
-
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
-
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
Answers
-
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
-
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.
-
Is the data above already in a second sheet?
-
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.
-
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
-
Thanks, how do I insert a formula in the drop-down contact cell? it's not letting me type.
-
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.
-
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 :)
-
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.
-
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
-
You are absolutely right. I corrected my mistake and now it works :) Thank you.
-
Glad we were able find the issue. Thanks for reaching out to the community
cheers,
Kelly
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives