-
Copy a Formula to Multiple Rows, but Keep One Column Ref in the Formula the Same?
Hey all! I understand that when you copy a formula with criteria in certain columns to adjacent columns, it changes the column references to keep the relationship between columns the same (ie, column 10 has a formula that references col 1 & col 3, and copying the formula from col 10 to col 11 would result in the col 11…
-
Need help with formula to COUNTM in a cross-sheet cell when Unique ID on both sheets MATCH
Hello. To enable dashboard reporting and visuals I am building a "Metrics" sheet that interprets data from a ginormous list (aka "G-List") -a separate sheet. Both sheets have [Unique ID], and G-List has approx. 40 multiselect dropdown columns (one of which is [Strategy 2023-2027]). I want my Metrics sheet to display each…
-
Date range in formula to consolidate monthly totals for other sheets
Help would be appreciated. I have multiple sheets with order details in them. I would like to create a sheet that I can have monthly totals side by side for each SKU and each pricing tier. So I thought I would just try referencing one sheet to make sure it worked before adding the other sheets, but when it come to date…
-
Formula help
Hi, I'm new to this forum and not sure how to properly set-up my question but I am working on an accounts receivable report. Column Names: Invoice Amount | Date Submitted | 0-30 | 31-60 | 61-90 | 90+ (aging columns) Looking for a formula that will populate the Invoice Amount value in the appropriate aging column based on…
-
vlookup/index across 2 different sheets
Hi everyone, I am trying to build out a workflow between two smart sheets - Our end goal is to have a user select from a drop-down list of numbers which once selected will then populate across the columns in the sheet. The sheet with the master data it would be referencing has the same column headers, the challenge I am…
-
Extract the first word from a cell
Hi, In 1 column I have a string that has 1 word then 2 or more words after that, how do I extract from the column the first word only? In excel I'd use the following =TRIM(LEFT(A2, FIND("^",SUBSTITUTE(A2, " ", "^",1)&"^"))) In Smartsheet there's no TRIM.. The below formula returns the value of the first and second word,…
-
VLOOKUP Lag In Updating
Hi all - I'm connecting two sheets via a VLOOKUP to get a value (Status). I'm seeing a 2+ min lag in the delay in the update of the VLOOKUP - which I've never seen before. It's always been nearly automatic. Even though there are only a few formulas, the VLOOKUP spans only two columns and there are only 50 or so rows, the…
-
Using most recently added row to populate dashboard
Hi - I'm building a dashboard for project reporting, and need the most recently updated row from my data sheet to be used to populate the Metric Widgets. The data needs to come from a sheet rather than a report as I want to display the data split out into areas, not all in one place. I've done a lot of reading, and so far…
-
Subtract date from previous row
Hi, i need to subtract a date-time rows from consecutive row ID as a formula. example: Row ID: JA001, Row Date-time: 01/09/23 10:53 AM Row ID: JA002, Row Date-time: 01/09/23 10:49 AM can you help me? Regards
-
Join/Collect? Formula to record text in a row on if a box is checked on sheet one ...
I am trying to put a formula on Sheet 1 in Column 2 that references Sheet 2, column 2 (DBE)( checkbox) - if the box is checked on Sheet 2, The text from Column 1 (Vendor) will all populate with a comma between each on Column 2 on Sheet 1 SHEET 1 SHEET 2 Thank you! I'm stumped on this one!
