-
Using Collect to pull data from the same sheet
I usually use Index(collect to reference separate sheets to pull data, but I'm trying to apply the same logic for pulling column values from the same sheet. I tried this as an Index(collect formula =INDEX(COLLECT([QTY on Order]:[QTY on Order], [Ref PN 1]:[Ref PN 1], [Part Number]@row),1 & a collect formula on its own,…
-
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…
-
INDEX COLLECT function returning #INCORRECT COLUMN VALUE
I have a sheet that contains the info of all of my employees which includes a column for their initials. The training department of my company has a sheet that I have view access to but not editor access. They track the qualification due date of all of my employees and I want to collect the due date on my employee info…
-
How to add another calculation to an If statement and Index/Match
Hi, Hopefully this will be an easy one. My brain is going completely blank on this. I have a formula that is basically calculating/selecting one of three values based on which is not empty. It is based on the user checking the "Included" box to trigger an index/match formula to show the cost (Hourly, ,Unit or Fixed), and…
-
Cross sheet functioning column formulas working perfectly...except for two rows?
Problem Summary: I have a series of formulas and cross sheet references which grab display data that meet certain conditions. They are working consistently across usage…except for two rows? For data sensitivity, I'll only be including the date fields out of the display data, but the same error applies to all final display…
-
Indexing & Sheet Updates
Anyone that uses sheet to sheet indexing noticing that sheets are not updating unless you open, save and refresh the data sheet that is the source sheet? We have had several team members reach out to let us know that their dashboards and reports are not updating due to this situation.
-
How do I use Cell reference in a formula when using Index?
I have a simple table that has the following columns (along with other columns), ZZZ YYY 10 15 and I would like to replace the ZZZ and YYY in the following formula =INDEX([Thing1]ZZZ:[Thing2]YYY, 1, 1) With the data from the columns above so that the resulting formula would be: =INDEX([Thing1]10:[Thing2]15, 1, 1)
-
Contact Field in Index Match Returning Strange Values
I have a project plan template that is created through the control center. The owner of certain tasks is dependent on what department the project is in. I have a background sheet that is a task owner crosswalk, and then on the project plan it will run an index / match to pull the correct owner. This has been working fine…
-
Fix #INVALID COLUMN VALUE error when using Index Match in a different column type than source sheet
Hello! I am curious if there is a solve to the #INVALID COLUMN VALUE error I get when trying to pull a value from another sheet using Index Match, and both the source sheet and current sheet have different column types. I am currently using the following formula: =IFERROR(INDEX({OChM - Intake - Intake Meeting Date},…
-
Help with index/collect (or maybe something else?)
Hi, I am struggling with index collect. I'm wondering if what I'm trying to do may not be possible with Smartsheet but I'm not giving up until I ask you wonderful people! I have a large Smartsheet with many columns and I'm maxed out at 381 columns, my license is only to 400 columns so while I could probably figure this out…