Lookup Value based off Data Validation List / Populate Form

**snake10** · 02-14-2008, 10:02 AM

Hello,

I am working on a gradebook for a teacher and am running into a little problem.

I am trying to make filling out an interim a little easier by having a validation list of the student names and populating different cells with the various grade by subject.

I have attached the sheet.

I have the Interim on the last tab in the sheet.
I have the validation list in A9, which pulls the names from the TOTALS tab.
Once the name is selected from the validation list I want to pull the grades from the TOTALS tab and place them in cells D44:D48 as seen on the interim.

I would imagine it would be a combination of INDIRECT and VLOOKUP but I don't quite get how to get this to work.

Any help would be greatly appreciated.

Mike

**NBVC** · 02-14-2008, 10:05 AM

Can you re-attach the sheet saved as an XL2003 file?

**EdMac** · 02-14-2008, 10:08 AM

Hi,

This attachmemnt is in 2007 format which many of us can't open - can you save your book in 2003 format and then ZIP it.

Ed

**oldchippy** · 02-14-2008, 10:12 AM

Hi,

In D44

=VLOOKUP($A$9,TOTALS!$A$2:$H$25,2,FALSE)

D45

=VLOOKUP($A$9,TOTALS!$A$2:$H$25,3,FALSE)

D46

=VLOOKUP($A$9,TOTALS!$A$2:$H$25,4,FALSE)

D47

=VLOOKUP($A$9,TOTALS!$A$2:$H$25,5,FALSE)

D48

=VLOOKUP($A$9,TOTALS!$A$2:$H$25,6,FALSE)

Format the cells to 2 decimal places

**snake10** · 02-14-2008, 10:13 AM

Sorry about that...

**oldchippy** · 02-14-2008, 10:14 AM

Originally Posted by NBVC

Can you re-attach the sheet saved as an XL2003 file?

Hi NBVC,

To many late nights in the Maternity Ward, you probably missing this one

http://support.microsoft.com/kb/925180

**EdMac** · 02-14-2008, 10:18 AM

Thanks for the link OldChippy,

Ed

**snake10** · 02-14-2008, 10:25 AM

Originally Posted by oldchippy

Hi,

In D44

=VLOOKUP($A$9,TOTALS!$A$2:$H$25,2,FALSE)

D45

=VLOOKUP($A$9,TOTALS!$A$2:$H$25,3,FALSE)

D46

=VLOOKUP($A$9,TOTALS!$A$2:$H$25,4,FALSE)

D47

=VLOOKUP($A$9,TOTALS!$A$2:$H$25,5,FALSE)

D48

=VLOOKUP($A$9,TOTALS!$A$2:$H$25,6,FALSE)

Format the cells to 2 decimal places

I was hoping that I could do it somewhat more automatically. Ideally, I would like her (the teacher) to select the name and then print it essentially.

**NBVC** · 02-14-2008, 10:31 AM

Originally Posted by oldchippy

Hi NBVC,

To many late nights in the Maternity Ward, you probably missing this one

http://support.microsoft.com/kb/925180

Thanks Oldchippy.... I knew about the converter but was kinda' reluctant to install it previously.... but I just broke down and installed it. It worked...

...too many late nights indeed

**oldchippy** · 02-14-2008, 10:34 AM

I was hoping that I could do it somewhat more automatically. Ideally, I would like her (the teacher) to select the name and then print it essentially.

Once you have put the formulas in place, you only need to select the pupil from the drop down and the grades will be refreshed to the new pupil

**snake10** · 02-14-2008, 10:39 AM

Originally Posted by oldchippy

Once you have put the formulas in place, you only need to select the pupil from the drop down and the grades will be refreshed to the new pupil

Beautiful! I can't thank you enough

**oldchippy** · 02-14-2008, 10:41 AM

No problem glad to help, thanks for the feedback

**BigD** · 03-22-2009, 02:28 PM

oldchippy...I tried using the formulas in similar situation...no help so far. I have an excel PO form on one sheet and and inventory list on another sheet. The inventory list is over 5000 items. the po has these cells: Item#, Desc, cost, qty, total. At present I have to search the inventory sheet for each item I want to order from a vendor, which is sorted on the item#. Once I do that I have to then copy and paste to the PO. The inventory sheet has cells: item#, Desc, Cost.

What I want to do is type the item # into the item# cell and have the formula populate the desc and cost cells from the inventory sheet, I then enter the qty and my simple formula gives me the totals. If I have 100 items to order you can see how this would become very time consuming.

Thanks D.

**shg** · 03-22-2009, 02:57 PM

Welcome to the forum, BigD.

Please take a few minutes to read the forum rules about posting questions in other people's threads, and then start your own.