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

Navigation
1

At school I work with a file with class worksheets (the name of the worksheet is the class number, eg 100, 200, ...).

I need to make a spreadsheet in this file to generate Declarations, where I need to select the Class, and do this load the names of the students. Select the student and load the student's data.

Is it possible to do this without VBA (I do not know VBA = [)?

I can make the Declaration setting the values:

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

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

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

    
asked by anonymous 27.06.2018 / 14:33

1 answer

1

I do not know how your spreadsheet and data are organized, but looking at the formula you created can do the following: 

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

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

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

For your second doubt it is exactly the same. Define 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 can not ask a new question by entering the code you've tried and an image illustrating the layout of the data.

    
28.06.2018 / 20:00
'app-welcome' is not a known element Angular 2+ Create DELPHI Component at Runtime