-
Referencing a predecessor row
It would be great to reference another row dynamically by using the value in another column. For example, I would love to be able to reference the values of a column in a predecessor row using the predecessor column value. I would love to be able to use a formula like: =[Actual Finish Date]@row - [Actual Finish…
-
Need help with sheet reference formula to grab assigned user for a specific day.
I am working on some cross sheet data and sharing and would like to list in a cell of a sheet the user [Technician] assigned to a space [Bldg-Room] on Thursday from another sheet. There is a column for each day of the week on the sheet being referenced. [Thursday] I can use this formula within the current sheet but I seem…
-
Attempting to create a formula to find items assigned to a contact from a drop down.
I hope my title makes sense. I work in a veterinary Setting, I am working to make some metrics for a sheet dashboard and I am trying to get a cell formula to show me how many of a category is assigned to a person. I have been able to figure out the totals per category in my sheet but now I want to be able to see it at the…
-
Strange INVALID VALUE error
Hi everyone, I've encountered a problem in Smartsheet that I tried solving for a long time without success. I was creating metrics for a sheet, and I made a bunch of summary fields that contained my formulas. They all worked as intended when I created them. About a month passed without anyone touching them, I went back to…
-
Linking a field in a form to a source sheet as picklist
I already put in a ticket and got the answer this is currently not possible, but was encouraged to put in a post here to hopefully being integrated one day. In my situation we have a huge list of project names (and constantly growing) that we'd like to be able to pick from in a from related to a SmartSheet. We find that…
-
Question about Templates and Formula References
Hi, I am creating a template of a "Cost Estimator" which will be created into a new sheet each time I have a new project. The Cost Estimator sheet will link to a master Project List using the Project ID and the Project Name. What I am attempting to do is create a Cost Estimator "Totals" sheet which will have a row linked…
-
Could a referenced cell formula be messing up my SumIf Formula?
I have two sheets. Sheet A with a list of Projects, Project ID, and Amount of Funds awarded. Sheet B contains order information. The information is taken through a FormStack Form, but on this form, the project owner selects their project name that is basically a list of their project combined with the project ID. Once that…
-
Outgoing Cell References
Is there any way to find where all outgoing cell references go? What sheets, DataMesh, etc. are referencing a particular sheet, even if I don't have access to the requesting asset, what assets are using this sheet? The activity log does not appear to show when formulas in other sheets reference the data. I can see when…
-
Help With Index-Collect and Cross Sheet Referencing
Hi all, I am attempting to return a Contractor's name from a source sheet using multiple criteria (if they are subcontracting and what their scope of work is). The issue I am running into is the unique ID I use to connect the sheets (The PCM Number) has duplicate values when there are subcontractors (multiple contractors…
-
Reference or copy a column in one sheet to another sheet automatically
Hi, I am trying to figure out a way to automatically get specific columns in one sheet, into another sheet I was originally looking at using a workflow to copy the rows over, but I only wanted to copy a subset of the columns which doesn't look to be possible. Then I started looking at cross-sheet references, but the posts…
-
Is this a bug and if so, how do I mitigate it?
I have a formula to autopopulate a drop-down list field to show one or more items, grouping together a number of related rows in another table. =JOIN(INDEX(DISTINCT(COLLECT({EcoTech Activities Managers}, {Activities PIDs}, =ID@row)), 0), CHAR(10)) Basically collect the names of managers from another table where the key…
-
How can I auto-populate a formula in a column that is referencing values in another sheet?
I have the formula below that is referencing the Planned Date and Exit Shop Date, but when I drag the formula through the column it doesn't update these dates as well? What I want to do is as I drag the formula down a row I also want the Planned Date and Exit Shop Date to move down a row and not remain static Is this…
-
Using multi-select dropdown list as criteria for Index/Match Lookup with SUMIFS
Hi all — Looking for some guidance on the below… I have a sheet named "Tracker" with a dropdown column of job levels (named "Target Audience") and another column that I am wanting to sum the job levels from whatever is selected for that row ("Total Target"). The totals for each job level are pulling from as a cross sheet…
-
Display date and duration by referencing other cells in the same sheet
Hello Everyone, I'm trying to reference data from row 44 into row 3 in the same sheet. "=[Task Name]44" works like a charm (see below). However, =[Start]44 doesn't result in the date "08/13/25" being reflected in row 3. Likewise, =[Finish]@44 doesn't reflect the Finish date from row 44. =[Duration]@44 or =Duration44…
-
Anybody Find a Workaround for Accessing Sheet Summary Fields Across Sheets?
I would like to be able to access Sheet Summary fields from sheet to sheet. You can access them from Reports as well as Dashboard Metric Widgets, but cannot access them by cell linking or references from other sheets. Has anybody found an work around? My attempt at a workaround: I tried creating a hidden column that grabs…
-
Cell Linking And Referencing Sheet Summary Fields From Other Sheet
Sheet1 has Sheet Summary fields. Sheet1 can access it's own Sheet Summary fields. A Report can access the Sheet Summary fields from Sheet1 (or any other sheet). A Dashboard Metric Widget can access the Sheet Summary fields from Sheet1 (or any other sheet). - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -…
-
Help with referencing data that is input through a form
Hi all, I'm hoping someone can help me with my specific issue that I cannot seem to resolve. In our Workspace that is set up through control centre, we have a sheet called a Checklist that is populated for each of our provisioned projects. The information in the sheet is populated via a form, as there is around 50 columns…
-
Stop Formula reference from applying to whole column/sheet
Hello, I have a formula I am trying to apply to only certain cells. See below: =IF(ISNUMBER([Column15]@row), IF([Column15]@row >= {LL5}, "5", IF([Column15]@row >= {LL4}, "4", IF([Column15]@row >= {LL3}, "3", IF([Column15]@row >= {LL2}, "2", "1")))), " ") Then I want to copy and paste this formula to a different cell on the…
-
Update a referenced sheet without individual column formulas
HI, I have a master sheet that feeds data into a helper sheet. I want the helper sheet to automatically update whenever the master sheet data changes. Is there a way to sync all columns based on a unique row ID (to avoid pulling in the duplication of rows), without having to write individual formulas for each column? I've…
-
Update a referenced sheet without individual column formulas
HI, I have a master sheet that feeds data into a helper sheet. I want the helper sheet to automatically update whenever the master sheet data changes. Is there a way to sync all columns based on a unique row ID (to avoid pulling in the duplication of rows), without having to write individual formulas for each column? I've…