-
Vlookup with cross sheet reference
Can someone please provide an example of the Vlookup formula with the cross reference? I have tried both formulas below and get errors on both. =VLOOKUP([New Job Code]1, {Lot Status Report - Goldmark Range 3}, {Lot Status Report - Goldmark Range 3}2, false) #UNPARSEABLE =VLOOKUP([New Job Code]1, {Lot Status Report -…
-
Roll Up Harvey Balls
I am currently working on a tracking page where I've been asked to create a "Progress" column using Harvey Balls. The goal is to use empty, half and full to represent not started, in progress and complete by training module (children) and then overall. Below is one of the many formulas I've been working on.…
-
countif column values based on a monthly value
Hi, I am creating a table of count of values in a column based on what month is reported in another. the table I want is attached. The formula I am using is as below. Can anyone advise what need to change in the planned formula =SUMIF{V2.0 Eirabot - Gantt Range 1}},"12", {V2.0 Eirabot - Gantt Range 1} I also want to have…
-
VLOOKUP/Matching/Collecting
Hi there, When a prospective customer fills out an online form and provides us with information, I want to be able to automatically match the specified services to one of our advisors from a separate database I have built. I understand VLOOKUP for one search value from one lookup table, but how can I specify multiple…
-
Preserving Approval Date
I heard in an ENGAGE 2018 video on alerts is that you can set an alert to request approval based on the values of one of the cells in the row. You can choose a column with drop-down properties to indicate that the alert was Submitted, Declined or Approved. Someone at the session asked if there is a way to know the date of…
-
Help with INDEX MATCH COLLECT formula
Hi all, I'm having trouble with a lookup-type function returning the wrong values. We have our base sheet called the "Deal Tracker" where we keep tabs on the various deals that we consider (we're an investment firm). When entering a new deal, we always collect a Contact Name of the person who brought us the deal. We often…
-
Harvey Ball Formula
Needing a formula to turn Harvey Red, Yellow, and green. If sold box is checked = green. If Quote Submitted day is over 14 days, AND sold is NOT CHECKED = yellow. And if Quote submitted date is over 30 days past, AND SOLD is not checked = red. I have the green part correct =IF((Sold@row = 1, "Green"). Having trouble with…
-
No Array formulas but need work around
Smartsheet does not use array formulas, but I need a way to list out items from one spreadsheet to another if a certain condition is met. For example, I have an array formula that I used in excel that would look for a certain condition on one spreadsheet then pull all the rows of data that had that condition to another…
-
Cross sheet formula help
I have a master property sheet that is updated with new property information. One of the columns in the property information sheet is "Sales Rep." I created another sheet that I want to track the list of properties for each sales rep. 1 sheet is setup for each sales rep. I would like for the tracking sheet to pull all of…
-
Locking or freezing the TODAY() formula
Good day all, I've looked around in the community about locking or freezing a current date, but I'm still having issues. My goal is to lock a start date that's triggered by entering a X and have that current date freeze until the x is removed. When an X is entered in the Visit column, that triggers a start date to…
