Reference cell value on another sheet based on the column value

Nick Armendariz

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!

Genevieve P.

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

Nick Armendariz

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.

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

Nick Armendariz

Hi @Genevieve P

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

Nick-

Genevieve P.

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

Happy to help!