Index Collect formulae

Hi,

Please help me to write the index collect to populate the status on Sheet 1 as marked in Red



Answers

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭

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


  • Gaurav Chauhan
    Gaurav Chauhan ✭✭✭✭✭✭

    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


    @sandeep kumar

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!