Autocomplete (and autolookup) as I type plus copy data

[higgalls]

Hey,

I am not sure whether it is possible to do what I want to do, but hopefully I can.

Basically, I have a list of kids who are part of our Sunday morning Kids programs (we have 4 programs on a Sunday morning - this sheet is called "Child Info"), and in another sheet, I want to be able to track attendance across each program.

What I would like to be able to do, if possible, is when I go to the sheet where I want to track attendance (which will be blank at the start of each term), for this example we will say the new attendance sheet is "Creche 2015", I want to be able to start typing a kids name, as I type it looks in the list on the full kids list sheet, and autocompletes it, from the data in sheet "Child Info" in column A.

So, if I start typing "Ch", then it will want to match it with the first name that starts with "Ch", which might be Charlie. If I don't want Charlie, but rather Chris, then I type the next letter, and it will then search again till it finds Chris. Then, when it has found the right kids name, then it will pull in data from some of the other Rows for that child (say Rows C, D, F & G).

Any idea whether this is even possible, and if so, how I can go about doing it?

Thanks heaps.

Cheers,
Chris

[TonyB51]

Hi,

I sounds like you need a List Box, found under Data Validation.

refer to the attached example.

I think it will work for you.

Cheers

TonyB

[higgalls]

Hi,

I sounds like you need a List Box, found under Data Validation.

refer to the attached example.

I think it will work for you.

Cheers

TonyB

Hey Tony,

Thanks heaps for your help. I had totally forgot to give Data Validation a try (I had done it with some other things in the spreadsheet).

However, one slight issue - it won't autocomplete as you type. It requires you to either select the child's name from the drop-down list, or you need to type the full name exactly. Hope this makes sense.

[higgalls]

Actually, another option if I go the Data Validation path is, is it possible to build the list of names, for the validation list, IF the value in the E column is equal to "Creche" for example?

[TonyB51]

Hi, Again

Generally speaking, as I am aware of, for an autocomplete as you are typing is when the item exists within the same column, generally above it.

A way to day this is to still have you list on a separate sheet so that it can be maintained, but allocate a specific number of rows above your starting point, or insert those rows above you starting point.

Then at the top, say from A1 have it look at the first item on the Listing sheet, then fill down to the top of your starting point.

Then simply hide everything above the starting point.

This should work, try it.

Cheers

TonyB

[higgalls]

Hi, Again

Generally speaking, as I am aware of, for an autocomplete as you are typing is when the item exists within the same column, generally above it.

A way to day this is to still have you list on a separate sheet so that it can be maintained, but allocate a specific number of rows above your starting point, or insert those rows above you starting point.

Then at the top, say from A1 have it look at the first item on the Listing sheet, then fill down to the top of your starting point.

Then simply hide everything above the starting point.

This should work, try it.

Cheers

TonyB

Thanks for that. I think I would prefer not to have heaps of hidden data all over the place. May get out of control.

What about my other option of using a conditional statement for the Data Validation? Say if E3-E200="Creche", then it can put the kids name (A3-A200) in the drop-down list? I have been trying to figure out how to do this, but haven't had any luck unfortunately. Any ideas what the Data Validation Range formula would be for something like that? If only VLOOKUP (and the similar functions) was more flexible.

Thanks heaps for the help.

[higgalls]

Ok, so I have been doing some more searching and I have come across something that has been done before and it looks like an awesome solution. Not perfect, but pretty damn close.

Can the solution by NVBC (here: http://www.excelforum.com/excel-form...tion-list.html) be adapted into mine? So I have all the kids info listed in the sheet "Child info" (dummy data at the moment), and then in the sheet "2015 - Creche), from A7 down, it would be good to have a pull-down menu of all the kids that have "Creche" listed as the program they are in, in the Child Sheet (column E), then to put their names in the pull-down menu (i.e. Data Validation), under 2015-Creche sheet. Then, preferably, if a child is then listed, for the next box, their name is removed from the list.

From what I can see, NVBC's solution seems exactly like this, but I unfortunately don't understand all the formula's in his example enough to adapt it for my use.

Thanks heaps.