Data Validation - I forgot how I used it

I made this wonderful spread sheet tracking the dental records of 212 children our volunteer clinic had evaluated. I made a separate sheet for each class and listed the students by row. There are 33 data points, name, age, DOB etc etc. Then I figured out how to automatically transfer an individual's data to a prefabricated "dental Chart" sheet. It worked wonderfully. The problem is that I now do not remember how I got it to work. Can I send the data to someone and have them figure it out for me? I know it involved "Data", "validation" and "List". I was able to produce a .pdf file for each kid with his name, age, and dental particulars which I then saved to dropbox. I can send you a link to the .xlsx file as well as the .pdf file so you can see what I was working with. Sorry to be a bother.. It's tough getting old and keeping up with all this wonderful technology!

If you post the .xlsx and .pdf files on the forum I would be happy to take a look.

OK here are the attached files

My understanding is it was forgotten how validation was used. I believe the goal is to have a dropdown (Data Validation) on the first sheet, that will then populate the values of that first sheet from the correct entry in one of the other sheets. I was going to take a look at it later but busy at work now.

I think that is right. I was trying to transport the data (gender, age etc) from the grade sheets to the dental chart sheet. I remembered a formula in the top left hand corner of the Dnetal chart sheet but it disappeared and I don't know how.

I will attach the version that I posted up on dropbox last summer. I have looked at it however and was unable to find my dropdown list.

Dental Clinic Database.xlsx

I have attached the beginning of what you need. The cells in red on the first page currently update based on the "Grade" and "Name" dropdowns. I'm not sure what all the data mean and where you want them, but using the vlookup formula, you can just change the column index number to pull whatever piece of data you need.

Let me know if this is what you need and if you have any questions about doing the rest yourself.

p.s. kinder looks like it is formatted differently so it will not work.

-Melk

Wow. That looks really different. I remember something about a VLOOKUP but it's hazy. SO how do I use this?? Did you change the name box? It has both first and last names in it. How do I change to the next student? And do I just cut and paste for the # of baby teeth and stuff like that? I really appreciate your efforts but I am not very good at this stuff. Why they elected me to be the "data guy" I'll never know.

Hey,

So yes, on each of the Grade Tabs I used a formula that combines that first and last name (Last, First) and put that in the first column (inserted a column into A)

Assuming each combination of first and last name per grade is unique, there should be one record for each student per grade

In cell B3 (really B3:M3) you select the grade (click and select from the dropdown)
In cell N3 (really N3:AE3) you select the student name (click and select from the dropdown)

Right now, that will automatically pull DOB: in AF3, Gender in BA3, and Age in BE4

If you look at the formula in AF3 (DOB)

Formula:

Please Login or Register  to view this content.

It is looking at B3 (grade) to select the sheet to look on and N3 (student) to select the record on that sheet. The '8' in the formula selects the 8th column of data (count from 'Fullname' in column A to 'DOB' in column H) if you wanted to select 'Age' it would be column 7 or 'Date of TX' it would be column 9. Basically whatever column of information you want to pull, you put that number in the formula above right before "FALSE".

Hope that makes it a bit more clear but let me know if you have further questions.

I am so sorry but when I look at $B$3, it just says "Fifth Grade". I would think it should be a drop down menu like the one you placed on the Summary sheet. And again in $N$3, it just says, "Mancinas, Daniella", I would think there again would be a drop down menu to select each student. Lacking these I still do not appreciate how the whole things works. Will all the data change when I change the student name or do I have to select each data column and change it individually?

Hey,

So there may be a compatibility issue between our versions of excel. On my version, if you click into B3 (or N3) it opens up a drop down (see attached screen shot)

dropdown screenshot.png

What version of excel for mac are you using? I can see if there is compatibility and/or show you how to add data validation lists on your own.

I have also attached the workbook in an older format. Maybe that will work. Other than the compatibility idea, I'm really not sure why or how your drop down menus are not working.

Dental Clinic Database.xls

14.2.3


i still can't see the drop downs

Ok. I have a mac running office 2011 at home, so I'll take a look in a couple of hours and let you know if I can straighten it out.

-Melk

You are so sweet to help me out like this.

Hey, not a problem. All I would ask is that you click the little star for my reputation and mark the thread solved once we get through this.

So I opened the workbook on my mac (Excel 2011) and everything seems to be working fine for me. When I select B3 (or N3) there is a little up/down arrow in the bottom right of the cell, this will open the dropdown.

Here is what I can suggest, you add the data validation yourself, all the pieces are in place already. If you go to cell B3 for instance, select the 'Data' tab -> 'Validate' -> 'Data Validation..' under 'Allow:' select 'List' in the formula box type '=Grades' (no quote marks)

Lets see if that works for you. If it does I'll walk you through the rest, if not something else must be wrong.

-Melk

I had already done that. When I click on all those, I can see that the cells with the grades on the summary sheet are highlighted, there is like a box around those cells that indicates they have been selected. So the formula is there, it just doesn't bring up the list, just the one grade

I'm really not sure what is going on. When you go through the data validation process, is the "In-Cell dropdown" option selected?

The bottom right of the cell should have a up/down arrow.. no idea why it wouldn't be showing up.

The in cell drop down was not selected. Now I see the dropdown list.

Why is there a date in BR5?

I am changing the other data points and aside from having difficulties in copying formulas to cell that are merged in a different configuration, I am getting it done. I had to recalculate the age for each TX date, but I figured that out. You said the kinder was different. I can't see that.

Thank you so much for being so patient with me. All the kids in the program thank you also!

Should I protect the Dental Chart sheet? I am trying to hide A1 and A4 and it won't let me do it unless the sheet is protected. Is that a wise thing to do?

Happy to help. The date in BR5 is just leftover from me figuring out the formula, you can delete it.

Looking at Kinder, it is just that Columns R through AC (found on every other sheet) don't appear to be there.

The easiest way to copy the formula is to select a cell with the formula, and copy it directly from the formula bar up top (where it says fx) then you can paste the text directly into any cell and change the number for the respective column.

Originally Posted by krisvadale

Should I protect the Dental Chart sheet? I am trying to hide A1 and A4 and it won't let me do it unless the sheet is protected. Is that a wise thing to do?

A4 is also left over from me figuring things out, you can delete the "H8" that is in there.

A1 you could place into another column (make sure you cut it not just copy and paste) and hide that column or you could format the text as white (so it will appear hidden). I'm not sure about hiding an individual cell but you shouldn't need to protect the workbook unless that is something you want to do for other reasons.

The problem is that I only included the table to Column AC (i.e. row 29) if you change that A1 cell to A1:AD100 that should fix your problem.

How magic!!

How do I give you a good review?

I will definately do that.

I am now trying to make the dental chart notations automatically transfer to the next set, IE: If a child has all his permanent teeth for an exam in March, he is going to have them all (probably) the following Sept. I would like the blue colored notations, if colored blue, to automatically be colored blue in the next chart down. I have looked all over google and am trying to learn conditional formatting but nothing is working. Should I start a new thread?

Hey, glad I could help. Sounds like you should probably make a new thread for that as it is a different question. I will be happy to help you though.

I'll keep an eye out for your new thread.

-Melk