Excel: load list of names according to the selection of another list

Asked

Viewed 1,233 times

1

in the school I work has a file with the class spreadsheets (the name of the spreadsheet is the class number. Ex: 100, 200, ...).

I need to make a spreadsheet in this same file to generate Statements, where I need to select the Class, and then load the students' names. Select the student and upload their data.

It is possible to do this without VBA (I don’t know VBA =[ )?

I can make the Declaration by setting the values:

Ex: create a list of class 100, select the student, and to read the date of birth use the formula: =DESLOC('100'! $B$3;CORRESP(F14;'100'! $B$3:'100'! $B$52;0)-1;1;1;1)

What I need is that when selecting another class the reference of the function changes to the selected class (instead of 100, 200 for example. It does not need to be a selection, it can be for example the value typed in a cell.

And would it also be possible, after selecting the class (or reading its cell number), to change the list of students? Data validation says it does not accept formulas.

excel vba list spreadsheets

  • I may be wrong because I don’t know much more of Xel in my view and only do a lot of IF and Otherwise

  • Because it’s Romulo, I want to simplify hehe

1 answer

1

I don’t know how is organized your spreadsheet and the data, but looking at the formula you created can do so:

=DESLOC(A1 !$B$3;CORRESP(F14; A1 !$B$3: A1 !$B$52;0)-1;1;1)

That way, if you put 100 in the cell A1 he will return the student data of the class 100, put 200 in the cell A1 will return the students of the class 200 and so on.

I suggest you take a look at the formula =Procv(). You will be able to do what you want using far fewer formulas and without so much complication.

For your second doubt, it’s exactly the same. Set a cell where you will put the class code and use a procv to return the name, procv to return the age, note, etc.

If you are unable to ask a new question stating the code you tried and an image showing the layout of the data.

  • So Max, it didn’t work that way. The data looks like this: 1) File 'classes.xls' 2) 1 sheet for each class. The name of the spreadsheet is the class number. Ex: 100, 200, ... When typing the class number and pressing 'enter' the class data must be loaded from the corresponding worksheet.

  • My problem: 1º - the list of students to select My list is fixed. Example with class 100: '=100! $B$1:$B$60' 2º - when selecting the student display the corresponding information Example to pick the date of birth of a student in class 100: =DESLOC('100'! $B$3;CORRESP(A2;'100'! $B$1:'100'! $B$60;0)-1;1;1) A2 is where the student’s name list is. The line below didn’t work: =DESLOC(A1 ! $B$1;CORRESP(A2; A1 ! $B$1: A1 ! $B$60;0)-1;1;1;1)

  • I solved, I used indirect

Browser other questions tagged excel vba list spreadsheets

You are not signed in. Login or sign up in order to post.