-
Index Match does not work for hyperlinks to Smartsheet Item
I use Index Match to pull the project meta data from another sheet and one of the data elements to pull is the Project Dashboard Link, which is a hyperlink to a Smartsheet Dashboard. The link is not being pulled across only the Display Value/Text. Is there a way to pull the link across? I read somewhere that the workaround…
-
COUNTIF with Parent rows only, exclude children
I want to create a COUNTIF function that will count only the Parent row of a status column in a project tracking sheet. This is to create a metric sheet that will show all project statuses by each project manager's sheet. There is a status column using Green/Yellow/Red/Blue identifiers, and children rows need to include…
-
Problems with Formula: COUNTIFS to not include certain text
Hello. I am trying to create a formula that helps me to summarize the information on my master sheet so I can create a dashboard. I am trying to sum up the number of courses between published and archived and the formula would collect data from two different columns (status and product type). Is it possible? I tried to…
-
How to use advanced formulas to pull information from two sheets into new (third) sheet
Hello everyone! I am hoping to get some tips/advice on using Index Match/Collect (I think is the right route). Currently, my company has one of our data sources tracked and managed through smartsheet. My goal is to make a new sheet, that is populated from pulling the data from our primary data source BUT I want to filter…
-
Complicated IF this THEN that FORMULA
So, I have a pretty complicated formula request: IF [Approved/Contracted Budget $$ (Total)] IS blank look in [Per Pt Costs (Submission)] Column and take that Number and put it in [Per Pts Costs (FINAL)] Column; however, if [Approved/Contracted Budget $$ (Total)] is completed, use that amount ($$) and put it in the [Per Pts…
-
Recording Week Tasks Were Completed
Hi all! I'm back with an old problem that rattled me a few months ago. Basically, when a task is completed, its date of completion is saved. For the sake of being able to a process this data in a more comprehensive manner, we want to take that date, and assign it a "weekOfCompletion". Another note, is that the weeks here…
-
Can I use a formula in a checkbox cell to auto check and also be able to manually check the box.
I am using empty checkboxes to show a task needs to be done. Only certain rows will require this task. So I wrote a formula to auto check any box that will not need to be done. That way a report will only tell me empty checkboxes. Problem is, I need the users to be able to check those boxes when they are done with the…
-
Having two currencies in one sheet
I currently manage vendors in Japan but need to also have currency in dollar for our finance/accounting in the US. I think the only current option is to change the whole account setting to change the currency, but that will prevent me from having two currencies, and will also change the entire setting to Japanese. Is there…
-
What formula to use in Status column based on % Complete column?
Hi, I'm stumped...I've tried this formula multiple different ways and nothing seems to work. Status column: Not Started, In Progress, or Complete % Complete column: any number (column is set to %'s) This is what I need: IF % Complete = 0, Status=Not Started, IF % Complete is greater than 0 and less than 100, Status=In…
-
I am writing a column formula and get invalid operation error
I am using this formula to calculate % of task time but get invalid operation error. Task time is the amount of time spent doing a particular task divided by the shift hours (9) =[Total Task Time]@row / 9