Help with returning a value.

[InfinityCola]

Hey, I'm pretty much a beginner when it comes to some of the formulas in excel, I'm struggling with a simple problem on my page currently.

I'm building an excel for my students, I currently only have two sheets.

Students and Week 1.

On the students sheet in Column A1 is a list of all the students, Column B1 is Week 1, C1 is Week 2 etc.

on the Week 1 sheet, I have Column A1 a list of all the activities from A3 to A50.

in column B1 i have the blank cell where the students name will go once they have chosen the activity they wish to take part in.

Then whatever activity is listed in A1-50 will be put into a cell on the Students sheet under week 1 next to there name

i currently have a formula this =if('Week 1'!B3:I50="John Smith" so it will search all of the blocks for the name I place in the available spots, but now im stuck, trying to get the formula to post the activity from A1 into the students sheet next to the students name.

here is a screenshot of the excel to see if it helps, hopefully it makes sense, all help is appreciated!

Thank you so much

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[InfinityCola]

Ah thank you, Im using 365

 

[Fluff]

Thanks for that, how about

=CONCAT(IF('Week 1'!$B$3:$I$50=A2,'Week 1'!$A$3:$A$50,""))

 

[InfinityCola]

Thank you for the quick response, so the way I envisioned it, would be that the formula locates the specific names that are put in the open spots, I currently have a list that is data validated of all of the students, I obviously cant post there names for security reasons, and used John Smith as an example.

So if you blocked John smith in one of the white open spots on "Beach" for example, Beach would be the word that appears next to his name under the week one column on the student list.

And i would repeat the formula for week 2,3,4 etc

Thanks again

 

[Fluff]

That's what the formula does, assuming that the students name is in A2 on the Students sheet.

 

[InfinityCola]

I'm just getting a blank space at the moment, i have placed the top student in the first placement

Thank you again!

 

[Fluff]

That looks to be in row 3 so you need to change the A2 to A3

 

[InfinityCola]

Wow. Thank you so much, I didn't see that, you've truly saved me a fair amount of time!

 

[Fluff]

You're welcome & thanks for the feedback.