-
Referencing an imported sheet
My workflow requires me to update a big chunk of data every week. It's about 7500 lines so I cannot copy-paste it, I can only import it from excel. Once the data is in smartsheet, I use vlookup to reference the values of specific columns in another sheet. The issue is that each import creates a new file so my vlookup…
-
Populate Quarter based upon date
I would like to populate a field called Quarter based upon Week Start Date field. All I need in the Quarter field is Q1, for example. Q1 = Jan - March Q2 = April - June Q3 = July - Sept Q4 = Oct - Dec I have tried to figure out the formula, but haven't been successful. I have manually been adding the information into the…
-
Adding Current Month Data To Dashboard and Metric Widgets
Hello Community, 1st Question: I am creating a dashboard for our compliance statistics. I have built out a dashboard calculation sheet based off other sheets as seen in the image below. I am looking to add only the current months lable and associated data to my dashboard to keep it clean. 2nd Question: I also have the…
-
Search and find the last sequential duplicate key in a Smartsheet?
I have a target sheet that has duplicate keys where the keys/rows can be found throughout the Smartsheet. I need to display the column named 2501_January value from the last sequential key's row in the target sheet. In this example, I want to search and find the last Row Key Rev-2 at the bottom of the Smartsheet from my…
-
Formula to search across multiple contact column
Hi, we are using the following formula, but it only works for calls with a single contact per cell, even though is looking into multiple contacts per cell. We managed to fix this by using a helper column that's the same as the contact column but set as text property. Is there a way to make the formula work with the…
-
Countifs for cells containing a letter
Hello, I have a formula to count how many hours of work I have assigned to me in a given week. It has the following inputs: "Owner" and "1st day of week" and it looks in a different sheet for all rows that meet that criteria and returns the estimated hours for the tasks. My formula is below and I want a solution for the…
-
Copying to Children Rows while Editing Parents - Best Practice Question
Hello! I have a large master sheet (900 rows) with ancestor levels of 0-4. Whatever is in ancestor level 1 in columns 1-12, I need that to copy down to ancestor 2 for all of the children of that parent. I need this to happen throughout the sheet. But I need the information to be fillable in ancestor 1 (not locked up with…
-
Rank Formula Multiple Results
I have previously received help with Ranking formula where there is more than 1 result it needs to rank the most recent week, which was resolved and worked fine - until last week. https://community.smartsheet.com/discussion/105462/duplicate-rank-what-formula-would-return-the-most-recent-week The new issue is that I have…
-
How do I only get the 25, 26, 27, etc out of 2025, 2026, 2027?
I need to pull out the last 3rd and 4th characters of a long year format CCYY where I only pull out the YY. So, in my example, I need to find the year 25 out of 2025. 26 out of the year 2026, 27 out of the year 2027 and so on. Basically ignore the first two characters of the long year 2025 so I will only be seeing the 25…
-
Pulling data from another sheet based on two criteria
Hello! I am having trouble getting this formula to work.. The goal is to pull the quantity of an item from a reference sheet to this sheet if the Spec #@row on this sheet matches an EAN on the reference sheet, and only if the Disposition value reads "Released". This is the formula I have cooked up, but get an…