-
Help with INDEX/COLLECT with a MAX/COLLECT
I am attempting to create a few reference columns using an INDEX/COLLECT function and incorporating a MAX/COLLECT because there is a 1-Many relationship on the RTO Queue ID in the 2nd sheet so I would like to use the row with the MAX Fully Executed Date. Below is the formula I am using: =INDEX(COLLECT({IA Assigned}, {RTO…
-
Combo a vlookup/index formula to pull a value from one sheet with cond formatting to another sheet?
Scenario: I have a Project A Plan sheet that has a field for Bill Rate and Role. This bill rate value is pulled in from a Client A Rate sheet via vlookup based on the value in the Role column on the project plan sheet. This formula is currently working fine. The Project A Plan sheet also has a column for employee name…
-
Cross Sheet Reference Shows #REF When Row Is Moved
I have an intake form setup in one sheet and in another sheet I have a bunch of formulas to format the data from the form into a csv that can be used to upload the information into Jira. I have everything working, except when I have the row removed from the intake sheet (because it's been completed) I get #REF in my sheet…
-
Index/Match Formula Behavior
Hello, I'm encountering an issue with the INDEX/MATCH function. When the value in the cell being match does not find a match and is recognized as text rather than a number, the formula does not return 'no match' as expected. Instead, it displays the last entry in the reference range. Formula in cell Category Index/Match…
-
Snapshot of Smartsheet comparing TODAY to Start and End Dates
I have a grid sheet full of contracts: each row is a contract. Each row has a START DATE and END DATE along with other details about the contract. I was tasked with creating a dashboard to represent this grid sheet over time. I am now creating a reference sheet to pull "snap-shots" of data from the grid sheet. I put in a…
-
Trouble with COUNTIF CONTAINS Formula
Hi there, I'm trying to build a summary sheet with formulas that COUNTIF cells in a column on another sheet CONTAIN a specific value (this column is multiselect dropdown). I've come up with things like the following, but am still getting errors. COUNTIFS({DCYF Impacts}:{DCYF Impacts}, CONTAINS("ASD - Administrative…
-
Please add "Select an Existing Sheet Reference" option when creating formulas.
When creating a formula you're given the option to "Reference Another Sheet". I love this feature but it needs improvement because it ONLY gives you the option to create a new reference. Yes, you can click the same range as an existing range and it will auto-populate with the correct range number, but that's only if you…
-
Pull value from cell to a new cell, and it won't change even if source value changes
I will use the following as an example for my problem: I have a pre-existing sheet ("Team Employees") where different teams record their # of employees once a month. This was used to track live staffing. The request is now to track overtime. My question is this: Is there a way to pull the # OF EMPLOYEES for LOGISTICS from…
-
Count checked boxes in a column from another sheet
Hi All, I am working on a dashboard that displays various info and tasks in state of completion. I'm wanting to count how may checkboxes in a column to display a chart in my dashbord showing % complete. Based on other posts I felt it was a good idea to create a helper sheet that has the formulas and reference into the main…
-
Absolute Reference changes if cell is moved
Hi, Looking for help with Absolute references in cell formulas. I need the formula reference to remain the same even if the user moves or copies the cells to a different column. The scenario is Column8 has the formula =SUM([Column 6]@row, [Column 7]@row) If a user moves the Column6 cell to Column9 , the reference in the…
