Reference cell value on another sheet based on the column value

Options

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.
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Nick Armendariz
    Options

    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
    edited 01/30/20
    Options

    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"))

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Nick Armendariz
    Options

    Hi @Genevieve P

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

    Nick-

  • Genevieve P.
    Options

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

    Happy to help!

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!