-
Index/Match instead Vlookup
Hi good day. I just created a formula to grab dates from another source sheet. The situation here that i created using VLOOKUP and read that it is recommendable to use Index/Match for this kind of situations. Can you help me to build the formula and including if there is #NO MATCH show in blank? Thanks in advance. Below…
-
COUNT IF, INDEX, MATCH
I have one table with a reference number, a sub-vessel number and a check box column to select the desired subvessel. I want to use a formula in another sheet to count the number of times a value is present in SOURCE(subvessel selection column), where the SOURCE(reference number) matches the TARGET(reference number) and if…
-
Capture Sheet Data Metrics by Current Year and Quarter
I am assisting our Marketing team with tracking social media statistics year over year broken out by quarter. I want to create a sheet summary field that captures metrics for the current year and a specific quarter to populate and auto-refresh on a dashboard to avoid manually modifying the metrics each quarter/year. Is…
-
Dropdown List Alternative with Automatic List Update and Dynamic List Features
There have been many requests for improvements to the Dropdown list from Smartsheet Community. The two most typical requests are Automatic updating of the list @Justin Mauzy @Taylor Compton - NOAA Federal Dynamically changing the list of the following selections based on previous selections @Ian Cooper @Mehran Bagherian…
-
Partial match with INDEX(DISTINCT(COLLECT(
Hello, I need to collect a list of values from my source sheet based on a search field (cell) in my target sheet. I have a very large subset of data that I'm pulling from, so I've divided it into 6 sheets to increase the speed of the lookup. Currently, I'm using the formula: =IFERROR(IF(OR(LEFT([Lookup Value]$2, 1) = "A",…
-
Help Summarizing Columns
Thanks for your help! I am looking to summarize in one column all of the reasons why something is red. For instance in row 2, I would like "why red" to be "Financial, Resources" because both of those two have red selections. Other than creating a helper column for each health status then joining them, is there a more…
-
Strange Request of Rounding Down against Laws of Mathematics
I've had a strange request. One that defines the laws of Mathematics: I'm calculating the time difference and then using this formula to round down. However, the formula is following the laws of mathematics and rounding up when it sees an 8 or 9. =MROUND(ROUNDDOWN(Difference@row), 5) E.g. In this example, the requestor…
-
Need help with formula IF PARENT, then INDEX/MATCH for Dupe Flag
First, thanks in advance for looking at my problem. I am sure I am making this more difficult than it has to be. New: Release Update Sheet Formula being added to this sheet in a Flag column Based on IF Parent Row This sheet has Parent AND Child rows Initial field for INDEX/MATCH is Release Column Release List Duplicate…
-
Need help removing blanks from a join collect formula
Hi, I'm trying to use a join collect formula that referencing a column in a helper sheet. =JOIN(COLLECT({Column I Want Data}, {Reference Number}, [Reference Number]@row), ",") It's pulling everything from {Column I Want Data}, that matches the [Reference Number] with {Reference Number} on the helper sheet and any cell that…
-
Output combined column name values from checkboxes.
Hi, I have a number of checkboxes, if they are checked I want to output the column value in another cell. I understand we can't reference the column values but I am able to do this: =IF([Red]1, "Red") This will return a single value if the checkbox is true. When I try something like this: =IF([Red]1, "Red", IF([Green]1,…
