Index Collect formulae
Hi,
Please help me to write the index collect to populate the status on Sheet 1 as marked in Red
Answers
-
I am not sure if the INDEX COLLECT will get you what you are looking for as you are looking across the columns and now rows in your Sheet 1.
May be the below helps:
=VLOOKUP([Name]@row,{Name to Training 2 Range in Sheet 1}, IF([Training Name]@row = "Training 1", 2, 3), True)
I would recommend that you add the value "Not Assigned" in Sheet 1 itself where you have blank cells today.
Not recommended but since you also want to set the Status value to "Not Assigned" if the cell in Sheet 1 is BLANK you will need to use,
=IF(ISBLANK(=VLOOKUP([Name]@row,{Name to Training 2 Range in Sheet 1}, IF([Training Name]@row = "Training 1", 2, 3), True)), "Not Assigned", =VLOOKUP([Name]@row,{Name to Training 2 Range in Sheet 1}, IF([Training Name]@row = "Training 1", 2, 3), True))
-
Thanks for your response. I am trying to collect the status of the trainings tied to the users on Sheet 1 marked in Red
Have re written the sheet for ref. Also, where are you collecting details of Status in the formulae
-
Hey @Gaurav Chauhan
Try this.
Training 1
=INDEX(COLLECT({Sheet 2 Status}, {Sheet 2 User email ID v2}, [User email ID}@row, {Sheet 2 Training Name}, "Training 1"),1)
Training 2
=INDEX(COLLECT({Sheet 2 Status}, {Sheet 2 User email ID v2}, [User email ID}@row, {Sheet 2 Training Name}, "Training 2"),1)
*Don't forget that you cannot simply copy paste the formulas above into your sheet. The cross sheet references must be individually inserted. Your range names will be different than mine unless you edit the name of the generically created range names prior to inserting in your sheet.
Do the formulas work for you?
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!