Conditional formatting and text string in array

[shaunholtz]

Hi guys,

i have a complicated question, that i'll try and descibe as best I
can.

A worksheet designed for soccer lineup. For example, E10:E14 is for a
certain position, Starting player, then subsitutes below. There are
several of these throughout the sheet.
Also, below is a list of the all the rostered players names. C40:I52

Currently, I manually type in the names into the above cells. What
I'd like to do be able to do is type part of the name into say E10 and
let it search the Array C40:I52 and auto fill in with the first name
that could be a match.

I do have some other funky formula and condtional formatting
happening.. The rostered names in the array are pulled from another
sheet, as well and as a name get's entered manually, it crosses out
the name in the array.

If you want a sample of the workbook, I can create one to test.

If someone has a better idea of how to accomplish this, please.. I'm
all ears.

Thanks!

 

[Bernie Deitrick]

Shaun,

What you are describing is called autocomplete. Under Tools choose Options,
then click the Edit tab, and check "enable autocomplete for cell values"

But here's the tricky part. Autocomplete requires a list of values - in the
same column, and in the same 'current region' - from which to choose.

The current region means that you have to be able to get from the cell that
you are entering values in to a cell in the same column, by traversing
filled out cells.

So, for example, if you put all the players names in B40:B55, and you wanted
to autocomplete your entry in cell B30, it wouldn't work. But, if cells A30
to A55 are also filled in (with labels, perhaps) then the autocomplete in
cell B30 _will_ work.

So, the bottom line is that you need to have a list of the players in every
column where you want to fill out their names.

An alternative is to use data validation and a list with a drop down - a few
more steps to fill in the cell, but easier than a manual fill in..

HTH,
Bernie
MS Excel MVP

 

[Storm_21_924]

Bernie,

Thanks for getting back to me.. Sorry it took so long to reply but
didn't get much chance to get online this weekend.
I don't think the autocomplete will work for me for this instance.
but your thought on Data Validation and a Drop down sounds
interesting.

let me give that a spin. I'll post if again if I have a problem with
the formulas.

Thanks!
Shaun