Fill the form in Sheet3 using data from Sheet1

I want to fill the form on Sheet3 for each school using the details from Sheet1.
I fill it manually each month which kills my time as well as my energy..
Please help me.!!

See if this does what you need. All I did was:
1) In Sheet1 added a column to determine Double or Single
2) Did an Advanced filter to get a list of the unique bus values.
3) Copied my list of busses to Sheet 3.
4) Set up formulas in E10:K36 to bring in the missing values. Wasn't sure how you were arriving at a "Shared School Name", so didn't do anything for that field.

the Detail on Sheet3 is not correct..

Have you tried to amend what jomili has done for you?

Alipezu,

The solution I provided was spot on with what you initially outlined in your problem ("I want to fill the form on Sheet3 for each school using the details from Sheet1."). It's incorrect to say "the Detail on Sheet3 is not correct." when what you really mean is "The result is not what I actually wanted. Let me refine my requirements". With all that said, the only difference needed on my initial solution is to change the list of buses to the list from each school. I've provided a formula that will do that for you. See the "Aljwood" sheet; it's all formula driven. You're welcome.

Alipezu,

Kindly check the attached file as per your requirement.
1. If you have new school,add the school name in school Tab
2. Don't delete the rows & columns where ever highlight in the Green colour in the Data Tab, Report Tab and school Tab
3. In report tab you have dropdown option for selection school Name & Tab
4. Don't rename the school type in you table.

I think this sheet will help you,

Rgds
Krishna

Originally Posted by alipezu

It looks like it's working as I wanted, I will check it later completely as I am very busy now.
Jomili you're a champion!!
I will'be very thankful to you if you give some details about the formulas you used as I have littel bigger table in some other workbook and I want to apply your formulas there.

Sure, always glad to explain.

Looking at the “Sheet1” table, Column H is the only one I added (don’t need J). That’s basically the same formula as you had in Column B, but instead of returning 1 or 2 I return “Single” or “Double”. I needed those values returned for the next steps.

Going to the “Aljood” sheet:
The hardest one to understand is the formula in C10 and down, which returns the Bus numbers for that school. Fortunately there’s a great explanation of this technique at https://fiveminutelessons.com/learn-...le-values-list The only difference between that explanation and my formula is that, since we’re working with a table, I used constructions like “IF(Table1[BoysSchool]=AlJood!$D$4” instead of “IF(‘Sheet1”!F2:F144=AlJood! D$4”.

For Bus Type, it’s just a straight forward VLookup from the Bus Number to return the Bus Type.

For the double or single Trip Types we compare the Bus Type to the left word in E8 or G8 (Large or Small) to determine in which column to do a Vlookup using the values in E9 through H9. This is why we needed “Double” or “Single” in the table, because it makes it simpler.

Still not sure what goes in “Shared School Name”, so nothing there.

For the driver, since the value is to the Left of the Bus Number in the Sheet1 table, a “normal” VLookup wouldn’t return the value (a “normal” VLookup looks only to the right). I COULD have done it by combining CHOOSE with VLookup, but I’m trying to train myself to use Index/Match more, and in looking to Left OR Right Index/Match excels. So, that’s what’s used to bring in the driver. If you’re not familiar with Index/Match you can find a lot about it on the internet. Here’s one place to get you started: https://www.ablebits.com/office-addi...ction-vlookup/

Jomili and Krishnakuma6,
I am very thankful for the help. krishnakuma6 has made my work little more easy with that dynamic report. Krishnakuma6 can you explain about those grey headings? as I am not that advanced excel user.
Thanks to both!!

Sure, I will explain in Evening, Morning I have meeting and lot of task to do

Krishna

Dear krishnaKuma6 and Jomili,
I am so thankful to both of you.
Today my company changed the database I was working on. Now there are no small or large bus columns, instead fewer columns in the Report Sheet. But the database1 sheet have too many columns and I think you can sort out that for me. I am attaching both the workbooks. If you guys can help me creating Report from Database1 on another workbook 'School Report'

Jomili and Krishna I appreciate your help alot and all my problems solved. Here is just one question. AlJood school has three sections KG, Boys and Girls and If there is a driver who has Two sections in AlJood, I would like he gets mentioned in the Report in two rows.
Please watch the attachments where I have highlighted yellow and that would help you understand what I need.
Thank you for the help.!