IF Statements and Linking Sheets by Ranges

hthoms ✭✭
edited 12/09/19 in Formulas and Functions


I am working on a project creating a weekly sheet for distribution where one week of info is pulled from a range of data on a separate 13-week sheet.  Basically, on the weekly sheet, I am using IF() statements to search a cell on the 13-week sheet for text.  If the cell contains the proper text, the name from that row on the 13-week sheet will populate on the weekly sheet.  I've included screenshots to provide a little more clarity.

The formula works fine, the issue is that I have to manually enter it in every cell because the references are linked to another sheet, and the IF statements are not dragging down sequentially like other formulas do.  To complete this automation would require 2,002 manual cell entries.  I am not the best with formulas, so is there a simpler way to do this and I'm just missing it?  Any help is greatly appreciated.


13-week Schedule.PNG

IF() formula used.PNG

Weekly Staffing Sheet.PNG


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!