Is there a function or formula to correct 1.1 hierarchy columns from being number and string values?
In this column I have highlighted the problem area. The smart sheet makes this into a decimal when there is one period, and makes removed the zero, breaking my hierarchy. I used this function in a helper column = Item@row + "" to make it into a string, but I'm left with the highlighted hierarchy as 1.1 instead of the required 1.10
Is there any way to lock the column type to only string data to fix the issue on import?
I need this to work because I will be using these hierarchies to determine which parent assembly the part is attached to. The data is Ctrl C (copied) from excel and Ctrl V pasted into smart sheet. Rather than make a helper excel file, I would prefer to have everything done in smart sheet so as to not complicate the process for the end user.
Thanks
Best Answer
-
I was able to make an auto number column that I could tie into an equation for it to look back for multiples of the hierarchy, and if one existed behind it, and the project numbers matched, add a zero, else just keep the value.
=IF(MIN(COLLECT(Item:Item, [SORT ID]:[SORT ID], <[SORT ID]@row, [EP Number]:[EP Number], =[EP Number]@row, Item:Item, =Item@row)) = Item@row, Item@row + "0", Item@row)
Answers
-
What is the formula you are using to populate the WBS column? Did you try adding + "" to the end of that formula?My apologies. I missed the last part of your post. Try using the "paste special" option when pasting the values in. You could also use the feature to adjust how many decimal points are displayed and then use the + "" method to convert it to a text string.
-
I've tried the decimal Idea, It adds zeros to everything so that won't work.
Ex.
1.8 -> 1.80
1.9 -> 1.90
1.1 -> 1.10
1.11 -> 1.11
I'll try paste special, that might be exactly what I'm looking for on the data entry side.
Edit*
Paste Special didn't work.
-
I see now. Sorry about that. You need one point one and one point ten, but you are getting two of either one depending on the method. Ok.
There are a few different WBS solutions floating around here in the Community that should be able to accomplish what you need.
-
What is WBS?
Thanks for taking a stab at it.
Edit*
What I have right now is a conditional formatting that highlights all numerical values in that column so I go through manually and add an apostrophe in front of problem ones. I'm not sure I can add a formula, because the data will be changing positions when things are sorted.
-
Take a look through this thread. This should have a solution that will work for you.
-
I was able to make an auto number column that I could tie into an equation for it to look back for multiples of the hierarchy, and if one existed behind it, and the project numbers matched, add a zero, else just keep the value.
=IF(MIN(COLLECT(Item:Item, [SORT ID]:[SORT ID], <[SORT ID]@row, [EP Number]:[EP Number], =[EP Number]@row, Item:Item, =Item@row)) = Item@row, Item@row + "0", Item@row)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 495 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!