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

[nathanmrs]

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.

[Pete_UK]

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

[nathanmrs]

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

[Pete_UK]

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

[nathanmrs]

Thanks for the information pete..

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

[Pete_UK]

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

[nathanmrs]

Hello again,

Hereby I had given my real data update and reattached the new file which clearly shows my requirement.

As I said there are 3 sheets in the work book.

1. Database sheet: This is the actual database from where the data has to be picked. It shows the status of each and every activity. if crossed(x) it means it is applicable. usually the database will be hidden sheet.

database.JPG


2. Interface sheet: This the sheet user uses to give input.. user selects the project category from frop down menu and also the preferred status. Based on these inputs in that particular drop down menu cells, it has to pick only the applicable activity and group using the cross (x) mark and drop it in the output sheet in the format shown in the output sheet.
interface.JPG

3. Output sheet: This sheet is the place where the user gets his output based on his input in drop down menu. It should show only the activites and groups which is applicable for that project category and status. He should be able to print it and mark some dates on it as shown in the image.
output.JPG



Also Please find attached the excel file which I want to automate. Please help to make it work. Excel automation help .xlsx

[Pete_UK]

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

[nathanmrs]

Thank you Pete..

Appreciate that..

[Pete_UK]

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:

=IFERROR(MATCH(ROWS($1:1),'Database sheet'!$K:$K,0),"-")

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:

=IF(OR($F19="-",$F19=""),"",INDEX('Database sheet'!A:A,$F19))

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