-
Allow viewing of Formulas on View only or Locked Shared Sheets
It would be great to be able to right click on a cell and "copy formula" or "Show formula" so that it could be copied, for viewable sheets. Or, create a sharing method that allows viewing of formulas. This is mainly for sheets that community users share as solutions to questions. It would allow us to actually see the…
-
Formula Help
Hi All - I am hoping someone might be able to help me. I am terrible at formulas and I cant seem to figure out how to pull what I need on my own so I copied a formula from another sheet we have at our company. The issue is that it returns it multiple times. So the expected result is "four", the formula returns "four"…
-
Converting email addresses with hyphenated First and/or Last Names
I am trying to convert email addresses into a Name field with the employee's First and Last Name, capitalizing the first character of each part of the name and keeping the hyphen in any part of the name that has one. I started with hyphenated first names so taking shmoe_joe-bob@company.com and converting it into Joe-Bob…
-
Formula calculating Status on Parent and Child Rows
Right now, the overall project status formula displays as "Complete" only if all the checklist items are marked as "Completed". We have been asked to update the formula so that it if all checklist items are marked "Completed" OR "Cancelled" the overall project status will be "Complete". The "Tasks Not Needed" references a…
-
Create a summary sheet
I want to create a sheet that computes all types of summary info from various other sheets. I have sheets called ProjectA, ProjectB, ProjectC. I also have a sheet called Summary-Info. In Summary-Info I want to create rows of metrics, such as Count of number of rows in ProjectA with Status="Done" Count of number of rows in…
-
Counting Columns
Is there a formula that allows me to count the number of columns in a sheet (to include in a sheet summary/)
-
Rolling 12 Months and Rolling 4 Quarters Formulas for Dashboard Charts
Hello, I have a metrics sheet that has static start and end dates for each month of 2023 and 2024. The requirements changed for the dashboard and management wants the dashboard charts to show data for a rolling 12 months and a rolling 4 quarters. This screenshot shows the column setup and formula I have for calculating the…
-
Replacing all special characters in form query string parameters with a formula
Hello everyone! I am excited to be speaking at the "Leveraging the Query String in Forms" session at Engage 23! I wanted to share a formula that a customer and I developed to replace any special characters that could show up in cell when you are building a URL to a form with query string parameters using a formula. This…
-
Dynamically referencing parent task row in COUNTIF/COUNT formula for child task completion ratio?
Hello Smartsheet Community, I'm trying to calculate the ratio of completed child tasks for a specific parent task in my sheet. Currently, I have a formula that works when I manually specify the parent task's row number: =COUNTIF(CHILDREN([Task Complete?]70), 1) / COUNT(CHILDREN([Task Complete?]70)) However, I want to make…
-
Searching more than one worksheet to return value
I am utilizing multiple worksheets (one for each year) but have one sheet that has some consolidated information, and I would like for it to be able to pull from the appropriate year sheet. =IFERROR(INDEX(COLLECT({Quote Log 2024 Quote Status}, {Quote Log 2024 Quote #}, @cell = [Quote Number]@row), 1), "") I would like for…
