-
NEW! Your survival guide to formulas is here. Meet the Formula Handbook.
Hi Community, Whether you’re summing child rows, calculating budget minus actual, or doing cross-sheet data lookups with multiple criteria, formulas are a must for tailoring solutions to your needs. The new Formula Handbook is here to help formula beginners and pros alike. This template contains: A glossary of all…
-
How to return multiple values from a sheet and paste them on multiple cells, based on criteria?
Hi, I have one sheet where there is a column with order numbers. There is also a shipping date column, which has a shipping date for each order number. The sheet itself is called Shipping. On another sheet, I want to return all order numbers of the next 3 weeks. I tried using the Index(Collect()) formula at first, but…
-
Outbound Cell Limit - Who's Calling Me? (Outbound Cell References)
@Andrée Starå @Paul Newcome @Genevieve P. I understand from a different post that there is a 25 million limit on outbound cell references. Is there any method to find out who's calling the master lookup (source) sheet? A list of all sheets that reference the Lookup sheet? What about pivots and DataMesh - any ability to…
-
Can I link 2 sheets with a formula that feeds many date columns?
Dear All, would you please advise if this is possible? if yes, would that be a combination of JOIN/ COLLECT/ IF / AND? If no, is there a way around it? I need the dates in these 10 columns: to feed into these 2 date columns: The condition is that Discovery Start Date has to feed into Phase Start Date when the activity is…
-
Getting Same Resutl with Slightly Different COUNTIF
Hi Guru's, I run the below formula and it gives me the correct number however; I modify the formula to look at another range however; I get the same result as the first formula minus the 2nd range. 1st formula =COUNTIFS([Operational Priority]:[Operational Priority], ="🔶", Status:Status, "In Progress") +…
-
% Complete Without Dates
Hey There, I work for a construction company, and one of our subdivisions has multiple townhome blocks. We are currently building 3 blocks, but don't know when we will start the other ones (all dependent on sales) I'm wondering if there is a way to get the % Complete for the whole project, not just the blocks currently in…
-
COUNTIFS not picking up some values - Name / Email
I am trying to count the number of Submissions by Submitter. The Submitter uses a Form to enter a line on the Sheet. They identify themselves by email address on the Form. The COUNTIFS formula I'm using at first seemed to get hung up on whether the form displayed a name (for the contact) or the email address. I've updated…
-
Formula guidance
Hi guys! I am trying to help fix a sheet that was built by someone else, and that person is no longer working for the company. They have a Dashboard that pulls data from a metrics sheet, that pulls data from a main data sheet. It looks to me like the sheet was moved or something, as it appears to have disconnected from the…
-
Having trouble comparing two cells
I'm having some issue with comparing two cells in my Smartsheet. I want the box to be checked if the cells match, and if not, the value of the one on the left should turn red. I have 8 sheets for 8 different budget accounts that are all based on the same template. This works for every one of them except for this one. In…
-
Create Table of Results Based on Matching Portion of Characters in Field (VLOOKUP or INDEX maybe?)
New to Smartsheet... Scenario: I have a list of treatments to apply based on various observations made. When I make an observation, various columns concatenate to give me an ObservationCode. Once the ObservationCode is generated, I want to interrogate the list of treatments and return all potential relevant treatments.…
-
Need help dialing in a grading formula [percentage to letter (100 to A+, 80 to B-, etc.)]
Hey all, This formula works most of the time, but it won't show the letter grade all the time, I think when its close to an in-between number. Any help making it function 100% of the time and not show an empty cell would be appreciated =IF([Week 04/01 Results]5 > 0.96, "A+", IF(AND([Week 04/01 Results]5 >= 0.93, [Week…