-
How do I write a formula to use multiple columns in a single cross-sheet reference.
I am trying to set up a "combined budget" sheet that will summarize multiple budgets while maintaining the heierarchy of the original sheets. Doing this on other solutions, I have previously just set up individual column references between each sheet (most projects utilize 2 separate budgets). This will not work on this…
-
Sum like data when information is stored in multiple cells on the same row
Hi. Any suggestions on how to sum data when it is stored across multiple columns? Each row contains up to three values and associated dollar amounts that come in via a form. I'd like to create a list of distinct values across all rows and sum the matching dollar amounts. Any suggestions? Thank you.
-
Need to reference distinct values from another sheet
I'm looking for a formula to automatically pull distinct values from a column in another sheet to use in a reference sheet. Example: Source sheet 1 has a list of [CustomerID] and various other columns. Some [CustomerID]'s are listed on multiple rows. Reference sheet 1 wants to list all the DISTINCT([CustomerID]) values, so…
-
Does VERY SLOW WiFi Break Cross Sheet Reference Calculations?
Our wifi is VERY SLOW today. I'm getting 20 Megabits down, but only .22 Mbps Upload speed. (NOT smartsheet's fault) When I am in a sheet that contains sheet references to another sheet, it APPEARS that the calculations change to true or false by themselves, with no other people making changes. I presume it's because…
-
Finding the oldest date on multiple sheets and also based on text in another cell
I have 2021, 2022, 2023, 2024, and 2025 sheets with different departments' requests for action (RFAs). I am looking for a formula to show me the oldest open RFA for each department. Some departments will be in 2021, but some will be in 2024. A sheet summary formula is not what I am looking for. I need a reference sheet…
-
HELP! Formula to Find Matching Value Between two Dates in another sheet
I need help creating a formula that I thought would be easy but is turning out to kick my be-hind, and three different Ai’s aren’t able to give me a solution either. Formula Objective: Find the Report Name located on the Report Deadline sheet, using the [Pay Date]@row, which will be a date between two date values on Report…
-
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…
-
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…
-
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…