Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.
Sign in to join the conversation:
We have a yearly schedule for all our field guys that is updated a week prior and sent out to the field so everyone knows where they will be working for the upcoming week. My question is can I tie a roll up sheet to the master schedule sheet that I would input a date on the roll up and based on that date grab the matching week portion of the master schedule so it can be sent out?
You could add a checkbox column and input the following:
=IF(WEEKNUMBER([Date Column Name]@row) = WEEKNUMBER(TODAY(7)), 1, 0)
What this does is look at which week number in the year your date is. It then compares it to which week number is 7 days after today (whatever today happens to be. If the week number match it will check the box, meaning the date falls within next week. This will always compare to whatever day it happens to be regardless of the actual date, so there will be no need to update it every time you get in there.
Drag fill on down (new rows will auto-populate). You could then hide the checkbox column to keep your sheet looking clean.
You would then either create a sheet filter or a report based off of that box being checked. I recommend a report as it is automatically going to pull that info. You could then set up an automated email to send out on whatever day you want, at whatever time you want, to whoever you want.
The only thing I have noticed (could be a personal setting but not entirely sure) is that the week number is based off of a Monday - Sunday week instead of a Sunday - Saturday week.
Would this still work if my dates are columns? So instead of my yearly schedule scrolling down the page you scroll across the page.
If your dates are set up as columns, you would use
=IF(WEEKNUMBER([Column Name]$3) = WEEKNUMBER(TODAY(7)), 1, 0)
In the BOLD portion you would leave the $ and change the 3 to whatever row number the date is in. You can then drag-fill across.
I am putting the formula in the row above the row with the date in it and I am getting an #Unpareseable error message. As you stated you make a new column that is formatted for checkboxes but I am wondering since my dates are in a row you would need a row of checkboxes which I don't believe you can do that in smartsheet?
Thanks for your help in advance!
What type of information do you have below the dates? Are the columns formatted as date columns or text/number type?
The columns are formatted as date columns and the information below the dates are the job locations where that person will be going for the week. So the first column in the spreadsheet has a list of names of employees and then the columns from there on out are date columns. Basically, we have a spreadsheet with 365 columns representing each day. The row the date is in has a basic formula that just adds 1 to the previous day to get the next date. I am sure there is a better way to do this but I have to get this to work first and then I can try and implement a more streamlined approach. I would like to think there could be a form that is filled out every week that is pulling employee names from the main schedule. It would just be week long and it would dump the information back into the main schedule or something along those lines.
Ah. Ok. To be completely honest... You may want to reconsider the layout of your sheet. Formulas in Date columns can only display a date without getting an error, and manually entering a date into a text/number column will give you one of two issues.
Either you use the =DATE and get an #INVALID COLUMN VALUE error since the column is not a date type, or any formulas referencing those cells as dates will not work since Smartsheet is not storing it as a date but as text.
You would have to write individual formulas for each different date using (for example) =WEEKNUMBER(DATE(2018, 01, 01)) then the next one, =WEEKNUMBER(DATE(2018, 01, 02)), and so on. Rather tedious and time consuming if doing all 365 days in the year.
I would suggest using a Date column as your first column and then use either the names or job locations as your column headers. Unfortunately your particular setup will require a lot of manual entry which kind of defeats the purpose.
Hi all, I am trying to build out a formula that will return "True" if the CIF #@row can be found above it in the same column (CIF #) AND the Days Since Created is between 30 - 365 days. All of the formulas I have tried have only gotten me so far as the "AND". Adding in the dates screws it up. The goal is to determine if…
I am getting a lot of REF errors for my predecessors and I can't figure out why. At first I thought I might have REF for predecessors to predecessors but that's not the case. When I export the file into Excel it shows the IDs in the place where it shows REF in SS. Each time I refresh the file I feel like different values…
I am trying to create a sheet summary, where the field is a formula that relies on a contact card within a column. However, it keeps returning a result of "#NO MATCH". The columns involved are, 1:) Priority (symbol column) for Up, Unchanged, and Down, and 2:) Contact Card column. I am trying to get a sheet summary formula…
