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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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-
-
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"))
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
-
Haha no worries! All it takes is one tiny thing.
Happy to help!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!