Formula to search for given term, and if not found in column to add it to list
 

Basically, I have a list of stock symbols in column A that goes down
about a thousand cells. I occasionally add to this list. Usually I just
search (or use find feature) to see if I already have a given stock on
the list, and if it is there then I ignore, but if it isn't I add it to
the bottom.

I want to create a cell / box / formula on top where I will be able to
enter a stock symbol (i.e., "IBM") and this box/cell/etc. will either
return that the symbol is already on the list, or if it is not on the
list will automatically add it to the list.

I have this so far which tells me only if a symbol already exists
=MATCH("IBM", A1:A500, 0) , which may be the wrong approach to start
with, and that helps to see if a symbol is on the list, though I need
something that will automatically add the symbol to the list if not
found.

Thanks for help in advance!!


--
financier
------------------------------------------------------------------------
financier's Profile: http://www.excelforum.com/member.php...o&userid=36260
View this thread: http://www.excelforum.com/showthread...hreadid=560523

Formula to search for given term, and if not found in column to add it to list
 

Hi!

To do exactly what you want will require VBA code. I can't help with that
but you could do almost the same thing using Data Validation.

You would "attempt" to add the symbol to the list. If it already exists the
validation will not let you enter it. If it doesn't exist the validation
will let you enter it. Is that something you might want to do?

Biff

"financier" wrote
in message ...

Basically, I have a list of stock symbols in column A that goes down
about a thousand cells. I occasionally add to this list. Usually I just
search (or use find feature) to see if I already have a given stock on
the list, and if it is there then I ignore, but if it isn't I add it to
the bottom.

I want to create a cell / box / formula on top where I will be able to
enter a stock symbol (i.e., "IBM") and this box/cell/etc. will either
return that the symbol is already on the list, or if it is not on the
list will automatically add it to the list.

I have this so far which tells me only if a symbol already exists
=MATCH("IBM", A1:A500, 0) , which may be the wrong approach to start
with, and that helps to see if a symbol is on the list, though I need
something that will automatically add the symbol to the list if not
found.

Thanks for help in advance!!


--
financier
------------------------------------------------------------------------
financier's Profile:
http://www.excelforum.com/member.php...o&userid=36260
View this thread: http://www.excelforum.com/showthread...hreadid=560523

Formula to search for given term, and if not found in column to add it to list
 

Thanks, I'll look into that. Any other ideas folks? I got the first half
down using a formula, to find that a symbol is there using MATCH, but
the rest I"m clueless on...


--
financier
------------------------------------------------------------------------
financier's Profile: http://www.excelforum.com/member.php...o&userid=36260
View this thread: http://www.excelforum.com/showthread...hreadid=560523

Formula to search for given term, and if not found in column to add it to list
 

You can't get a formula to "push" a value to another cell, so you could
not automatically add a missing item to the list - you will need VBA
for that.

Hope this helps.

Pete

financier wrote:
Thanks, I'll look into that. Any other ideas folks? I got the first half
down using a formula, to find that a symbol is there using MATCH, but
the rest I"m clueless on...


--
financier
------------------------------------------------------------------------
financier's Profile: http://www.excelforum.com/member.php...o&userid=36260
View this thread: http://www.excelforum.com/showthread...hreadid=560523