I want to make a widget with hours consumed.


So, I want to make a Widget in my dashboard that shows that hours consumed by my resources out of their allocated hours, for that i need to SUM all the hours they put on their TIME ENTRY sheet, i made the formula and in works on SHEET SUMMARY being =SUMIFS(HOURS:HOURS, EMPLOYEE:EMPLOYEE, "xxxxxxx") (xxxxxx being the actual name of the person) BUT in order to make a widget i need to create a new sheet with only the data a need, that being:

  1. the sum of hours that each resource has put on their sheet table which i got the number with the formula above.
  2. the allocated hours which i get from the PP

My problem is when i copy my formula to my new sheet and reference the TIME ENTRY sheet it doesnt work, and i dont know why, the formula i use is =SUMIFS({NAME OF THE SHEET Range 5} HOURS:HOURS, EMPLOYEE:EMPLOYEE, "xxxxxxx")

This are the widgets i need to make

This is the formula thats working of the Time entry sheet but it doesnt work on a new sheet with reference to this sheet.

Please Help!

Thank you.


  • Intern98
    Intern98 ✭✭✭

    Hi, you would have to reference the range for [employee]:[employee] in the new sheet as well,

    the formula would look something like

    =sumifs({range 1},{range2},"[primary column]@row")

    The area selected upon clicking 'reference another sheet' is the equivalent of [hour]:[hour]

    you would not see the actual column name of the original sheet.

    do let me know if that helped

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!