Best Of
IDEA: Slightly Offset Formula bar while editing so that you can see or click on referenced cells
I can't be the only person that is VERY bothered by having the formula editing bar in the SAME row as the row you're applying it to? It drives me nuts when I try to type in a formula and the editing window COMPLETELY COVERS the entire row, so I cannot see the columns/cells that the formula is referencing, nor can I click on the cells I want to reference.
I have 4 Solutions. All 4 would solve a lot of frustration :)
1. OFFSET the editing bar up slightly. Move the formula editing bar up just slightly. Make it only cover 80% of the current row and 20% of the row above it, instead of completely covering the current row. This would let us see if the cells behind it are highlighted and to click on the cells if we wish to add them to the formula. The percentages might need to be adjusted but the idea is the same (eg. 75/25%). Since MOST of the data would still remain on the same row, we would not be confused with which row we are editing.
2. Wrap the text in the formula editing window if it takes up at least 25% of the current window. This seems like it would be the simplest to implement.
3. If no text is typed for a few seconds, make the current formula window slightly transparent so that you can see if the cells behind it have colors. This option is not as good because you can't click on the cells behind the formula bar this way, but at least you can see them.
4. Allow us to press ALT+ENTER to move the text behind the cursor to the next row, without breaking the formula. This is currently possible if you a) Delete the = sign in the formula, b) navigate to the place you want a line break c) ALT+Enter d) Add = to the beginning of the formula.
Thank you!
NeilKY
Re: Pie Chart Color Consistency
@hbaksys I agree this would be very helpful and save a lot of time! I have the same standardized colors for status and having to update them each time is a detail I'd like to not have to think about.
Amy B
Re: Pie Chart Color Consistency
In our dashboards we use the same tracker (in the same Workspace) to generate various reports/widgets for different levels of status updates.
For example, 1 widget may report # of each type of status (4 New, 6 In Progress, 2 Ready for Review, 10 Closed - etc). Another widget needs to report % of each status (18% New, 27% In Progress, 10% Ready for Review, 45% Closed - etc).
Even though the dashboard and trackers are created from templates AND both widgets pull from the same tracker, the issue is both widgets do not default to the same color legend for "New", In Progress", "Closed" etc. "New" in the # widget may default to blue but "New" in the % widget may default to green once the templates are used to create a new workspace.
It would be very time saving if there was a a way to "lock" a specific color in a dashboard legend to a specific dropdown response when multiple widgets are pulling from the same column from the source document. Currently this is a manual process of aligning all widgets to the same colors for each status option.
TamieKing
Re: Introduce yourself & get to know your peers!
Hello, I manage the grants/awards for Walden University. I started using Smartsheet a few years ago. I swear I am the only one in the university who actually uses it. I use it to manage all of our award programs: application, pre-/post-award, and review process. I would like to figure out a way to use it to streamline the reviewer assignment process.
Kristina Harris
Re: Formula to Calculate Multiple Columns into a %
@Not so formula savvy I would manage this with 4 helper columns. I would have my sheet setup as follow:
Date Column - Set to Date Only
Yes Column - Set to either text entry, or drop down containing a "yes" value.
Text Column - Set to text entry
% Complete Column with percentage formatting applied with the following formula:
=IF([% Column Helper]@row = 3, 1, IF([% Column Helper]@row = 2, 0.66, IF([% Column Helper]@row = 1, 0.33, IF([% Column Helper]@row - 0, 0))))
Date Helper Column set to the following formula:
=IF(ISBLANK(Date@row), 0, 1)
Yes Helper Column set to the following formula:
=IF(ISBLANK(Yes@row), 0, 1)
Text Helper Column set to the following formula:
=IF(ISBLANK(Text@row), 0, 1)
% Complete Helper Column set to the following formula:
=SUM([Yes Helper]@row:[Date Helper]@row)
You can hide all of the helper columns, but for reference I've left them open below. It will produce the following results:
I hope this helps!
Regards,
Brian
Brian Wilson DC
Re: Formula to Calculate Multiple Columns into a %
@Not so formula savvy While this may technically be possible with the columns not being next to each other, it will be much more efficient if you did have them together. As for your concern with columns being added if they are all together, as long as the new column is added after the first column but before the last column (so anywhere in between the first and last) it will be picked up.
=COUNTIFS([First Column]@row:[Last Column]@row, @cell <> "") / COUNTIFS([First Column]@row:[Last Column]@row, OR(@cell = "", @cell <> ""))
Having them separated doesn't necessarily require helper columns until possibly we get to that one that has 60 fields, but that is simply to work within the character count restrictions. It also means that columns added in the sheet that need to be included will also need to be manually added to the formula(s) manually each time. The basic idea is that you string a series of IF statements that output 1 or 0 based on whether or not the field is blank and add them together and then divide by the total number of fields.
=(IF([First Column]@row <> "", 1, 0) + IF([Second Column]@row <> "", 1, 0) + IF([Third Column]@row <> "", 1, 0)) / 3
As you can see… The character count can quickly get up to that 4,000 limit in which case you would then need another column with a similar formula to house the next set of columns. Once all of these sections are created, you would then add them together in another column.
So the next column would be
=(IF([Fourth Column]@row <> "", 1, 0) + IF([Fifth Column]@row <> "", 1, 0) + IF([Sixth Column]@row <> "", 1, 0)) / 3
Then
=[First Section]@row + [Second Section]@row
Of course you would be able to have more than 3 in each section, but that is the general idea if you wanted to keep the columns separated.
Paul Newcome
Re: Are you planning to upgrade to Jira v10 in the near future?
Hi, we just updated our Jira from version 9 to 10 and I was surprised that the connector is not working with it.
When do you think the correction will be disponible ?
Thanks
Nicolas
NicolasDLP
Re: Power BI Report Failing : Connected to SmartSheet
It sounds like the data(columns) in Smartsheet have changed since your last refreash and it cant reload correctly. Try reestablishing your source in power BI and see if that will do it.
Nathan Slatton
Submitted Ideas Status
I'd like to see a summarized / categorized list of the submitted ideas and any status provided by SmartSheet of innovations underway. A possible ranking of items to be worked, next up, etc. Reading through the forums, some are very old and it's challenging to know if the item is being worked or not.
cschoenfeld
Re: Multiple Line Entries with one Form Submission
I have this question as well. Had to revert to Excel.
I have a form for occupants that utilize a room in a specific building (6 different buildings). Each occupant needs to have a percentage entered of time spent in the room (many, many rooms) and into 1 of 7 categories, which then need to total up to 100% for the room.
Right now, I have it set up with an option to "add another user" checkbox and then it will load the same previous information for input. Using this form, all additional users end up on the same line when it imports into the Worksheet. I need to have each occupant on their own line.
I wonder if there's a more efficient way.
Thank you! Lori
maineL