Best Of
Re: Registration now open! ✨ Join us for ENGAGE 2023: Potential unlocked
I see what you did there with "I cannot wait to engage" 😉 Can't wait to party with you!
Alison C.
Re: Excluding Weekends from Date formulas?
Okay, I think I get what you are saying now. These two formulas would replace the ones in your screenshots.
=IFERROR(WORKDAY([Start Date]11, -CEILING([Delay (Days)]@row + [Duration (Days)]@row, 1)), "")
=IFERROR(WORKDAY([Start Date]@row, CEILING([Delay (Days)]@row + [Duration (Days)]@row, 1)), "")
Carson Penticuff
INDEX and MATCH across two sheets: a detailed explanation
Hello everyone, would love some feedback on this tutorial.
I was answering a user's question regarding INDEX and MATCH (thread post here), and thought it would be beneficial to share my example to the greater Smartsheet community of how to easily utilize INDEX and MATCH functions to reference cells from another sheet by using a shared "identifier" value.
I couldn't find any sources of a true step-by-step tutorial of how to create sheet references to a second sheet range, then show how to utilize INDEX/MATCH to get the info from those ranges.
This tutorial includes a step-by-step example of how to create one sheet as a "data source" sheet, and a second sheet that's used for a more "public" sort of view. The idea here is for the data to have a "home" on the source sheet and a select set of columns available on the other sheet (useful for sharing when you don't want to share all the data from a sheet).
----
Documentation so you can get savvy:
Here's the documentation from Smartsheet for both Index and Match.
Below, I'll try and explain a layman's terms way to do INDEX MATCH - I'd recommend using INDEX/MATCH functions rather than VLOOKUP because INDEX/MATCH functions are more helpful if your data moves around (i.e., if one row gets moved, VLOOKUP can "break").
For your use case, you'll be utilizing what Smartsheet refers to as "Cross-sheet references". You can read about Cross Sheet Formulas (and there's a video) here: https://help.smartsheet.com/learning-track/smartsheet-advanced/cross-sheet-formulas
----
Due to limits on characters for posting, I'll add my solution as several responses to this discussion post.
Brett Wyrick
Re: Free Account Editors vs Paid Account Editors
It does appear as if it will only be the two of you that use Smartsheet, you may have been better off on the free plan as long as you only needed the two sheets. The free plan allows for the two users to be Viewers or Editors, whereas the Pro plan requires each Editor to have a license. It does seem odd that upgrading to Pro would actually limit your ability to use the tool the way you were on the Free plan, and I thought that must be incorrect. However, the following link does appear to confirm this is accurate:
Maybe someone else will chime in and explain something that both of us have missed.
dereksemmler
Re: Error - Formulas reference too many cells
Hi @cantpickname,
The total count of cells referenced by formulas in a sheet cannot exceed 25 million. This includes references to in-sheet cells, columns, ranges, and cross-sheet references.
To understand how this limit is calculated:
If a single cell has a formula that refers to 10 columns on a sheet with 10,000 rows, then that formula is referencing 100,000 cells (10columns x 10,000rows = 100,000cells).
However, if that formula exists in 100 different cells, then the total referred cells is 10,000,000 (100,000 referred cells * 100 formulas = 10-million)
Are any of your cross-sheet formulas VLOOKUP? If so, I would suggest replacing these with INDEX(MATCH functions instead. VLOOKUP will refer to an entire table range, while INDEX(MATCH only refers to the columns necessary to lookup and return a value.
Let me know if you need help adjusting your formulas!
Cheers,
Genevieve
Genevieve P.
Re: Update Request Customization - descriptions and headers like a Form
As with a form, it would be nice to have some type of help text the user can see when filling out the update request form. This will drastically improve the user experience and help us add value to users we ask to update data on our sheets.
Erik Hartman
Re: Contact Lists
Hi @alangham
I hope you're well and safe!
This is probably a perfect use case for the Premium App, Data Shuttle.
Would that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
Andrée Starå
Re: Chart Colors
@David Goshorn Yes, I'm afraid there are still niggly things about Charts, but like Paul has put here, you might need to submit an enhancement before they are sorted out. With the workaround from this thread in place you might find that changing the Pie Chart into a Semi circle type chart will help...drawn up a demo here to show the impact...
Hope this is useful
Kind regards
Debbie
Debbie Sawyer
Re: Unhide specific columns on a sheet
Hello,
Having to unhide all columns at once can be overwhelming for new users. From a developer stand point, it's horrible. I usually have to unhide all columns to develop automations and procedures through multiple sheets and reports. Just to properly set up a lookup function can be difficult for new employees who haven't used our procedures much.
It's not a big problem if 1 person is running the show, but as soon as multiple brains get put together and want to start developing procedures within their own departments, data can be easily forgotten and duplicated.
Some sort of task manager to show a list of the columns with the ability to possibly check/uncheck the individual columns that you want to be shown would be great! It can be set up on the right side bar and operate the same as the rest, within that bar, having the window that slides out after making a selection.
Like I mentioned above, as soon as there's more than 1 person working within the same sheet, confusion can easily be caused by a mis-click and the feeling of "starting over" the task (hiding columns) comes on.
MichaelTCA