Create a list in the output sheet by picking data from database, based on a cell value

Hey,

I am new to the forum and trying to find a way to pick applicable value from the database and put it in the output sheet based on a cell value.

Please refer to my attachment. There are three sheets in my file.

1. Database - It has a list of students name,and the information of subjects they had passed and failed.

2. Interface - User uses this sheet to give his input based on the dropdown menu. For example: if the user wants to see the students passed in English, then he has to select 'English' in subject(B3) and 'Passed' in the Status (E3) in Interface sheet.

3. Output -after the input is given in the B3 & E3 cells of Interface sheet, the output sheet should show the section, name of students.
Pick from database based on cell value.xlsx
But how to make it work in Excel, someone please help me. I am a user of basic excel features but a novice in such complex tasks.

I got just 10 more days to finish this project. I appreciate your quick responses.

This looks like a school project or homework to me. Please check out the Forum Rules and FAQ to see the stance that the Forum takes on this.

Pete

hello Pete,

No, i am not a school student and this is not a school project. That attachement is just an example to represent the objective of my activity.

That is just a easy example which i used to apply the same logic to my complex requirement.
I hope your misunderstanding will be clear by my response.

Thank you

Your attachment should be representative of your real data, with the same columnar layout as in your real file.

No contributor wants to spend time coming up with a solution only for the OP to say "my real data isn't like that".

Pete

Thanks for the information pete..

I will attach my real data.. but my concern is, it is quite huge..

It doesn't need to be the complete file - just a small number of records by which you can illustrate what you want to achieve. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Pete

It doesn't help that you have used merged cells, but I'll try and get round that.

I'll take a detailed look at this in the evening, after I've had something to eat.

Pete

Thank you Pete..

Appreciate that..

I've had to get rid of your merged cells in columns A and B, and replace them with individual values in each cell. However, I've applied conditional formatting to the cells so that it looks very similar to what you had before. I've put this formula in K3 of the Database sheet:

=IF(OR('Interface sheet'!$B$4="",'Interface sheet'!$D$4=""),"-",IF(INDEX('Database sheet'!D:I,0,MATCH('Interface sheet'!$B$4,'Database sheet'!$D$1:$I$1,0)+MATCH('Interface sheet'!$D$4,'Database sheet'!$D$2:$E$2,0)-1)="x",MAX(K$2:K2)+1,"-"))

and copied this down beyond your data. This helper column identifies the records that match the criteria set on the Interface sheet and gives them a unique sequential number.

On the Output sheet I've set up a table below yours and put this formula in F19:

Formula:

Please Login or Register  to view this content.

and copied it down. This determines the row(s) where the matching records occur on the Database sheet. Then I have this formula in A19:

Formula:

Please Login or Register  to view this content.

which can be copied across into B19:C19 - it retrieves the appropriate data from the row given in column F. This row of formulae can then be copied down as far as you need.

I've also applied conditional formatting to the cells in columns A and B to try to mimic your merged cells. You can delete your example table on this sheet if you want to move the dynamic one further up - the formulae should adjust automatically.

I'm not sure why you have set it up with criteria in a different sheet - it is a bit awkward to go to the Interface sheet to set the criteria and then click on the Output sheet to see the results. You could easily put the drop-downs to set the criteria at the top of the Output sheet, so that you could see the results straightaway.

Anyway, hope this helps.

Pete

P.S. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete