Cross sheet reference
I have two sheets. In one, employees enter the time they spend on each task. This gets totaled to the column [Total Minutes]. On the second sheet I want to only show date and total minutes. I have no problem doing only one row but when I try to reference a complete column I get the error. This is my formula for one row only:
=IF({Translator Productivity Tool Range 1} = "LZ", {Translator Productivity Tool Range 2})
where range 1 is the employee and range 2 are the total minutes. This works fine but then when I try to reference the whole column it stops working. Is the If function the best one to use for this?
Any help would be really appreciated.
Thanks,
Best Answer
-
Once you enter a date, you can use a formula along the lines of...
=INDEX(COLLECT({Other Sheet Total Minutes Column}, {Other Sheet Date Column}, Date@row, {Other Sheet Name Column}, "LZ"), 1)
Answers
-
Are you able to provide screenshots of both sheets with sensitive/confidential data removed, blocked and/or replaced with "dummy data" as needed?
-
Thank you, I appreciate your help.
-
-
-
I am unable to see any detail in your screenshots. Could you please repost with sample data?
-
Sorry about that.
-
-
-
You said you want to pull the number and the date, but I don't see any dates in your target sheet. Just "156".
-
The date can always be entered manually, as long as the minutes get pulled in.
-
Once you enter a date, you can use a formula along the lines of...
=INDEX(COLLECT({Other Sheet Total Minutes Column}, {Other Sheet Date Column}, Date@row, {Other Sheet Name Column}, "LZ"), 1)
-
It works perfectly! Thank you so much for all of your patience and help.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 437 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
