cell reference based on date

Patrick Eastman

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?

Paul Newcome

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.

Patrick Eastman

Would this still work if my dates are columns? So instead of my yearly schedule scrolling down the page you scroll across the page.

Paul Newcome

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.

Patrick Eastman

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!

Paul Newcome

What type of information do you have below the dates? Are the columns formatted as date columns or text/number type?

Patrick Eastman

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.

Paul Newcome

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.