Match names in a list

[fmb419]

I have a list of names to which I add a name each week. Each time I add a name to the list I would like to indicate somehow, (by highlighting, underlining, etc.) that name. And also, if possible, the instance in the list that the name matches! The list will not be in any order.

I've had experience with spreadsheets but it has been years since, so you might say I'm a novice.

I've been playing around with MATCH and LOOKUP but can't seem to put it all together. Can this be done with a function or a combination of functions?

Thanks for any help you can give.

Frank

[RusselB]

My suggestion is to use Conditional Formatting

[coray80]

Using conditional formatting I have created a sample file.

[fmb419]

Great! That's exactly what I want!
Unfortunately, I don't have the slightest idea how to use conditional formatting and the sample you sent doesn't allow me to see how you set it up. The cf option isn't highlighted when I select FORMAT on your sample.
I guess I need some really basic training on cf. I've read the explanations in HELP but it has raised more questions in my mind that I need answered in order for me to understand how it works, when to use it and where to use it.
I don't want to take up a lot of your time with this so maybe you can point me to where I can get basic information.

It looks like Conditional Formatting is the way to go. Thanks for pointing me in the right direction!

[coray80]

I don't understand - "The cf option isn't highlighted when I select FORMAT on your sample."?

search in you tube with 'conditional formatting in calc'
the formula you want is
cell value is
equal to
INDEX($A$2:$A$1000;COUNTA($A$2:$A$1000))

[fmb419]

Sorry it took so long to get back. Been trying to figure this out myself.

What I meant was that I downloaded your sheet and following the instructions on it. When I clicked on Format in the menu, the Conditional Formatting option in the drop-down menu isn't bold and when I click on it nothing happens.

[acknak]

Check that the file is not read-only (look in the window title).

If so, click the "edit file" button on the toolbar (fifth or so from the left) and save a new, writeable, copy of the file.

[fmb419]

Thanks acknak, that did it. Now I just have to figure out why and how coray80's solution works!! I really am a noob! I don't understand how 'index' and 'counta' are working together. I can't ask you to spend a lot of time with me but maybe you can give me some advice.

Thanks

[Villeroy]

=INDEX($A$2:$A$1000 ; COUNTA($A$2:$A$1000))
=INDEX( range ; row#) refers to the row#th row in range.
Think of the COUNTA(...)th row within $A$2:$A$1000

[fmb419]

I think I'm getting it but when I add a name to my list only the name in the cell I enter is formatted the way I want it but not the duplicate name in the list.
I entered:

=index(scott;(counta(scott)))

where scott is the range name of my list.

[Villeroy]

Scott does not expand when you append any values below. You need to do 2 things:
Go to Tools>Options>Calc>General and turn on the option "Expand references ....". This is a global option for all spreadsheet documents.
If scott refers to A1:100, you go to A101 and insert a a cell or row, then scott will expand to A1:A101. Any insertion of cells anywhere within a referenced range and directly below will expand the reference.
Without the option set, any insertion on top of the referenced range (A1) would shift down the range (scott moves down to A2:A101 without expanding) and any insertion below would not have any effect on the referenced range. Any insertion between A1 and the last cell A100 would expand the reference.
Using this clean method of range expansion you can also use =INDEX(scott;ROWS(scott)) which would work even when scott has blank cells.