Default values when creating a new row
I am attempting to create a new row that already has some content in it. I would like my "% Complete" column to have a 0% in it when a new row created. The other column is a 'Hierarchy Helper' column that is used for a formula. I would like this column to have a default value of 1 when a new row is created. I have been able to find ways to autofill a formula and formatting when new rows are created but not autofill a value.
Best Answer
-
Hello @M38a1 ,
I've attempted multiple ideas of how to implement default values, and I think the best way of doing this would be to either use Cell Links, or a Cross Sheet Formula.
If you were to use a Cross Sheet Formula, you could use a simple VLOOKUP Formula to look into another Sheet for the value of 1, and 0%. Please see my example below:
Sheet before adding a new row:
Sheet after adding a new task under the "Tasks" columns:
As Smartsheet has Auto Fill, it pulls down the Formula from the above rows. Also please note that to get a percent, you can use the percentage formatting button on your top toolbar. Here are the Formulas I used for the Default Value and Percentage Columns:
Default Value:=VLOOKUP(1, {Information Range 1}, 1, 0)
Percentage: =VLOOKUP(0, {Information Range 2}, 1, 0)
Here's how my other Sheet is set up, and where the Formulas pull from:
For more on Cross Sheet Formulas, VLOOKUP and Auto Fill, please see the below articles:
Cross Sheet Formulas: https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas
VLOOKUP: https://help.smartsheet.com/function/vlookup
Auto Fill: https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting
Answers
-
This may be helpful to understand what I am looking for.
-
Hello @M38a1 ,
I've attempted multiple ideas of how to implement default values, and I think the best way of doing this would be to either use Cell Links, or a Cross Sheet Formula.
If you were to use a Cross Sheet Formula, you could use a simple VLOOKUP Formula to look into another Sheet for the value of 1, and 0%. Please see my example below:
Sheet before adding a new row:
Sheet after adding a new task under the "Tasks" columns:
As Smartsheet has Auto Fill, it pulls down the Formula from the above rows. Also please note that to get a percent, you can use the percentage formatting button on your top toolbar. Here are the Formulas I used for the Default Value and Percentage Columns:
Default Value:=VLOOKUP(1, {Information Range 1}, 1, 0)
Percentage: =VLOOKUP(0, {Information Range 2}, 1, 0)
Here's how my other Sheet is set up, and where the Formulas pull from:
For more on Cross Sheet Formulas, VLOOKUP and Auto Fill, please see the below articles:
Cross Sheet Formulas: https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas
VLOOKUP: https://help.smartsheet.com/function/vlookup
Auto Fill: https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting
-
Would be a great enhancement to have default values as a column property...
-
Thanks @Sean Morgan - That solution seems like it would work since the formulas auto fill. I also agree with @Leibel S that this would be a great enhancement.
-
+1 for enhancement
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives