Help with returning a value.

InfinityCola

New Member
Joined
Feb 4, 2017
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

1623864978538.png


1623865124993.png
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,996
Office Version
  1. 365
Platform
  1. Windows
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’)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,996
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, how about
Excel Formula:
=CONCAT(IF('Week 1'!$B$3:$I$50=A2,'Week 1'!$A$3:$A$50,""))
 
Solution

InfinityCola

New Member
Joined
Feb 4, 2017
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,996
Office Version
  1. 365
Platform
  1. Windows
That's what the formula does, assuming that the students name is in A2 on the Students sheet.
 

InfinityCola

New Member
Joined
Feb 4, 2017
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Thank you again!

1623870174729.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,996
Office Version
  1. 365
Platform
  1. Windows
That looks to be in row 3 so you need to change the A2 to A3
 

InfinityCola

New Member
Joined
Feb 4, 2017
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Wow. Thank you so much, I didn't see that, you've truly saved me a fair amount of time!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,996
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,148,244
Messages
5,745,606
Members
423,964
Latest member
Rayds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top