Reference cell value on another sheet based on the column value

I'm trying to figure a way to add the value of Time Spent in one sheet and add it to another sheet based on the value of the Location and Date columns.

So if location is Auburn - A Street and is today's date add Time Spent to sheet 2 in column is A Street for today's date.

Hopefully that makes sense. I a bit new to this.

Thanks!

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Nick Armendariz

    You could use a JOIN(COLLECT formula for this, even though you don't have values to Join (you'll just be returning one cell, assuming that only one value meets all the criteria). Try this:


    =JOIN(COLLECT({Time Spent in First Sheet}, {Date in First Sheet}, Date@row, {Location in First Sheet}, "Auburn - A Street"))


    If you did have multiple Time Spent values for the same location and date, you could then add a comma between these values with the same formula, like so:

    =JOIN(COLLECT({Time Spent in First Sheet}, {Date in First Sheet}, Date@row, {Location in First Sheet}, "Auburn - A Street"), "","")

    Here are some Help Center articles I used that may help you: COLLECT Function / JOIN Function / Cross Sheet Formulas / Using @row

    Let me know if this works for you!

    Cheers,

    Genevieve

  • Hi @Genevieve P

    Thank you for the suggestion! I tried using the JOIN(COLLECT formula. Here's what is looks like:

    =JOIN(COLLECT([{DM Time Tracker - South Range 1}, {DM Time Tracker - South Range 2}, Date@row, {DM Time Tracker - South Range 3}, "Auburn - A Street"))

    • {DM Time Tracker - South Range 1} = Time Spent
    • {DM Time Tracker - South Range 2} = Date
    • {DM Time Tracker - South Range 3} = Location

    Unfortunately I'm getting #uparseable. I've should've mentioned that Time Spent cell has a formula in it, not sure if that makes a difference.

    Thanks,

    Nick-

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 01/30/20

    Hi @Nick Armendariz

    It looks like there's a [ in front of your first {cross sheet reference}, just after the COLLECT... try this, with it removed:

    =JOIN(COLLECT({DM Time Tracker - South Range 1}, {DM Time Tracker - South Range 2}, Date@row, {DM Time Tracker - South Range 3}, "Auburn - A Street"))

  • Hi @Genevieve P

    That worked! Such a small mistake that I missed. Thank you so much!

    Nick-

  • Genevieve P.
    Genevieve P. Employee Admin

    Haha no worries! All it takes is one tiny thing.

    Happy to help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!