Search Function that Returns a cell name?

Hi,
I need to have a function that will search a given worksheet
(or a range of cells on a given worksheet) for a string
and then report back the cell name where it was found.
In other words, if i want to look for the word "monkey" on a workshee
and "monkey" is in cell H83, I'd like it to return the value "H83". 
It seems that something like this would already exist as a canne
function, but I can't seem to find it.

If someone could suggest how to do this within Excel or failing that
within an Excel VBA Macro, i would greatly appreciate it.

Thanks

--
Message posted from http://www.ExcelForum.com

0
6/18/2004 6:45:38 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
399 Views

Similar Articles

[PageSpeed] 56

This worked ok for me:

=ADDRESS(MAX((Sheet1!A1:C9="monkey")*ROW(Sheet1!A1:C9)),
         MAX((Sheet1!A1:C9="monkey")*COLUMN(Sheet1!A1:C9)))

(all one cell)
This is an array formula.  Hit ctrl-shift-enter instead of enter.  If you do it
correctly, excel will wrap curly brackets {} around your formula.  (don't type
them yourself.)

Adjust that C9 to match the bottom right of what you want to search.  (all 4
spots!)

"Indra7 <" wrote:
> 
> Hi,
> I need to have a function that will search a given worksheet
> (or a range of cells on a given worksheet) for a string
> and then report back the cell name where it was found.
> In other words, if i want to look for the word "monkey" on a worksheet
> and "monkey" is in cell H83, I'd like it to return the value "H83".
> It seems that something like this would already exist as a canned
> function, but I can't seem to find it.
> 
> If someone could suggest how to do this within Excel or failing that,
> within an Excel VBA Macro, i would greatly appreciate it.
> 
> Thanks.
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
6/19/2004 1:25:46 AM
If the functions in the freely downloadable file at 
http://home.pacbell.net/beban are available to your workbook, the 
following will return the addresses of all the matches if array entered 
into a column of cells sufficiently large to accommodate the number of 
occurrences of the matches.

=ArrayMatch(soughtValue,A1:A6,"A",4)

The 4th argument controls whether the form of the addresses is $A$1, 
A$1, $A1, or A1.  Adding a fifth argument of True will make the function 
Case Matching.

If there is only one occurrence of the sought value it need not be array 
entered.  If not enough cells are selected for the returned values, it 
will return a message indicating the number of cells to be selected.

Alan Beban

Indra7 < wrote:

> Hi,
> I need to have a function that will search a given worksheet
> (or a range of cells on a given worksheet) for a string
> and then report back the cell name where it was found.
> In other words, if i want to look for the word "monkey" on a worksheet
> and "monkey" is in cell H83, I'd like it to return the value "H83". 
> It seems that something like this would already exist as a canned
> function, but I can't seem to find it.
> 
> If someone could suggest how to do this within Excel or failing that,
> within an Excel VBA Macro, i would greatly appreciate it.
> 
> Thanks.
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 
0
unavailable (273)
6/19/2004 5:55:58 AM
Reply:

Similar Artilces:

Search for an asterisk
How can I search for * in a spreadsheet? Everytime I put * in my search criteria, I get everything. Thanks, Cindee Hi cindee Use this ~* insted of * -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "cindee" <cindee@axisaccounting.com> wrote in message news:140d01c38c19$e8eccd10$a401280a@phx.gbl... > How can I search for * in a spreadsheet? Everytime I put > * in my search criteria, I get everything. > > Thanks, > > Cindee "cindee" <cindee@axisaccounting.com> wrote in message news:140d01c38c19$e8eccd10$a4...

Scroll function gone after update
I have a client that does not have scroll bars on edit lists since updating to version 10. Also, even if there is a scroll bar present, we're unable to scroll by rolling the ball on the mouse, we have to click the scroll bar-we didn't have to in version 9. I am unable to find anything regarding this in knowledgebase. Thank you for any help with this. Tracey D Trace D, I am not wrong , edit list is report. First click the "Magnify" field in report and click the any one of the report field. Now mouse scroll will work in report. regards Jeganeed...

How do I dynamically retrieve the cell address of the last cell t.
I want to write to an excel file that already has data in it using matlab xlswrite function. I found that I can write to the excel file without overwriting information that is already there. So I was wondering if there is a way in excel to retrieve the address or index of the last cell containing data. Nancy There are several ways of doing this depending on the layout of your data. Say you have data in all the cells of A1:A10. No blank cells in that range. You can use: LastCellAddress = Range("A1").End(xlDown).Address(0,0) This will give the address of A10. If you...

Date Function
I am running a query from a table that limits the output of data to a specific time frame. I am working on the last month minus 12 months. So what I want to see is from April of this year to May of last year. This is progressive as the months change. i have been using: Between (DateAdd("m",-1,Date())) And (DateAdd("m",-12,Date())) This gives me this years May which I do not want. Any help would be appreciated -- Message posted via http://www.accessmonster.com On Wed, 09 May 2007 19:18:04 GMT, "rollover99 via AccessMonster.com" <u14080@uwe> wrote: ...

UNIX inspired "which" function
I worked some today on my UNIX inspired "which" function. function which() { if ($args -ne $null) { $c = $args[0] $c + ' is a' get-command $c | % { $a = $_ switch ($a.CommandType) { 'Alias' {' alias for ' + $a.Definition + '; ' + $(which $a.Definition)} 'Function' {' function'} 'Application' {' application at ' + $a.Path} 'ExternalScript' {&#...

Search Beta Test :secure login without Password
Hello, We are now starting a limited public beta of a new security program for Windows named "NaturalLogin". The program can use one or more hardware devices (such as standard USB storage keys) and/or custom questions to enable a multi-factor (2 or more) login. We are looking for serious beta testers with the following configuration: - Windows NT, 2000 or XP; - Not connected to a domain manager (stand-alone PC or workgroup); - At least one USB removable storage device. If you are interested please register on our Web site at: http://www.palcott.com/betatest/ ...

copy text to specific cell
In one cell I have a String And in another cell I have a reference to a cell. How do I put the String into the referenced cell? A Formula will return a value to the cell that holds the formula. You'd need some VBA to do what you want. dim myStrCell as range dim myAddrCell as range with activesheet set mystrcell = .range("a1") set myaddrcell = .range("b9") .range(myaddrcell.value).value = mystrcell.value end with --- This has no validation at all! If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritch...

search for numbers with file/open/tools/search
I need to look for excel documents containing specific numbers. We used to do this using File / Open / tools / search. However after a new installation we cannot get any results back. If we search for plain text in the same documents, the result is shown. So it seems like searching for numbers is the problem. ...

/r/n (13-10) (carriage return
Hi, A few days go I fiund iut that the xmlserializer strips away /r/n out during deserialization (and just leave /n) ... I found out that you can resolve this problem using the deserialize overlaod that takes an xmltextreader instead of a stream .. However when it's time to Web Services you have no such a control over the deserialization phase AND I found out that WS strips out /r/n (an leave /n) ... I understand there are some XMl specs related to this .. however . what one should do to have a /r/n sequence into a string property of his own custom object ? I saw that writing /r/n...

Using the IF function
Ok I have A1 as test, A2:9 as blah, A10 as test and A11:A20 as blah so it looks like this test blah blah blah blah blah blah blah blah blah test blah blah blah blah blah blah blah blah blah blah What I want to do is make A1 so that IF(A1=???Any of cells A2:20??? then it becomes the same format as A1 is that possible -- fluc ----------------------------------------------------------------------- fluci's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2589 View this thread: http://www.excelforum.com/showthread.php?threadid=39291 You'd need a macro. A formul...

Relaying denied. IP name lookup failed
Hi I had activate de Routing restrictions on my Exchange 5.5 and I put the ip address of the hosts that can relay, but some times I recive an email with this message: Relaying denied. IP name lookup failed on the admin mail accaunt AnyBody, have an idea?? Thanks for all ...

Counting of cells with a specific color filled
Hello experts, I have an excel workhseet where different cells are filled with different colors. I need to count no of cells in a specific column with a specific colour filled. For example i need to count how many cells are filled with green color and how many with red etc. What formula i should use? Thanks in advance, Atiq use the COUNTIF() function, the criteria is whatever was used in conditional formatting to change the color fill of the cell. If conditional formatting was not used to color the cell, you can't do it. (unless you use Visual Basic programming) ...

I have pound marks in a cell rather than words/letters.
I can double click and get the words but when I move off the cell, it returns to pound marks, Stupid question, but did you increase the width of your column? "claudia riley" wrote: > I can double click and get the words but when I move off the cell, it returns > to pound marks, What you are actually seeing when you dbl+click is formulas that produce numerical results, but the cells have been formatted in some way (Currency, perhaps). The program is telling you that the columns are not wide enough to display the entire formatted content so it won't display anythin...

conditional formula for blank cells
I have a conditional cell color based on a formula to identify blank cells & populated cells. I want to now count the cells that meet the formula criteria. I get accurate counts with the populated cell formula count but because I have an isblank in my blank cell conditional formula I am getting a miscount. Is there any really good way to count colors based on conditional formulas especially where the condition is blank? You should have no trouble if use use COUNTBLANK. You should get a count of blank cells no matter how they are formatted -- Gary''s Student gsnu200705 &qu...

Newbie question / plug in a value for a cell based on a range for that value
Hey guys, New to Excel so sorry if this explanation isn't the clearest in terms of correct terminology. In one section of my spreadsheet I have a list of percentage values associated with numbers 1-20. For example #10 is listed with the percentage value of 38.4% in the cell next to it (O14 and P14). In another section of the spreadsheet I have a quick formula that I'd like to use these %'s based on what number (1-20) is entered. In my spreadsheet the % I want to use for this #10 is listed in P14 (38.4%). What can I plug into my formula so when I enter "10" is kno...

Excel "save as" function.
Microsoft excel XP will not let me save a document using the "Save as". How do I get this fucntion working? What happens when you try to use the Save As? Error message? Alert message? Nothing? Any other functions that don't appear to be working? Specifically File>Open? Read this google search result and see if anything applies. http://snipurl.com/ep4l Gord Dibben Excel MVP On Thu, 5 May 2005 03:38:02 -0700, help me please! <help me please!@discussions.microsoft.com> wrote: >Microsoft excel XP will not let me save a document using the "Save as"...

set cell value and workbook Creation date
How can I set the value of a cell with the creation date of the workbook? Each workbook is beaing created from a custom template. TIA W William, Either through a macro or through a User-Defined-Function: Sub TryNow() ActiveCell.Value = ActiveWorkbook.BuiltinDocumentProperties("Creation Date").Value End Sub Use this like =CD() Function CD() As Variant CD = Application.Caller.Parent.Parent.BuiltinDocumentProperties("Creation Date").Value End Function HTH, Bernie MS Excel MVP "William Hamilton" <william@NOthevirtualSPAM.PLEASEco.THANKSnz> wrote in me...

Return to the last accessed cell in a multiple paged file at reent
I have an Excel file at work that I did not create, with multible tabs (pages). Hyperlinks from the main page take me to the sub-pages. Some of the sub-pages open to the last accessed cell on that page, some take me to the top of that page. What can I do to make all pages open to the last accessed cell? ...

Fixing user with wrong Domain Logon Name
This is both a comment and question. When adding a new user I forgot to preface his Domain Logon Name with the domain. Instead of typing domain\username I simply typed username. There was warning message that was generated but then the user got created. I opened the newly created user in an attempt to fix my mistake but the Domain Logon Name field was greyed out. I "fixed" the problem by modifying the users' record in the SystemUserBase table in the CompanyName_MSCRM database. So far everything seems to be working but if any of you see a problem with how I fixed this ...

Autofill a range of cells based a a txt value in a separate cell
I have a speadsheet that has a function to display the day of the week based on the date. I want to color in all cells (several rows and columns) under this header to distinguish weekends. I will still need to input text into these cells on an occasional basis. I am new to Excel, and I am trying to automate what a predecessor took hours to do mannually. Thank you for your help in this matter C20driver --- Message posted from http://www.ExcelForum.com/ Hi have a look at 'Format - Conditional format'. >-----Original Message----- >I have a speadsheet that has a functi...

In Cell Editing
Hi there: Been using Excel for years but haven't asked this question until now... Could have saved myself a few days likely! Anyway, say I type BACON in a cell and press enter or tab - the focus moves to the cell below. If I arrow back up to the cell that has BACON in it, how do I start editing it without double clicking on it with the mouse? I don't want to delete BACON and start again, I want to edit BACON. After I'm done, I want to eat bacon. haha Thanks Press F2 -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in ...

Change the default cell size in Microsoft Excel?
I've been trying all morning to figure out how to change the default cell size in Microsoft Excel so that each cell is the size of four cells normally. Is this possible? I realize I can select four cells at a time and then merge them but I need page after page of cells this size and its not very efficient to do it this way. Thanks in advance, James Open an excel sheet and click on Format coloumn standard width then put in the width you need Thanks Simon -- simon ----------------------------------------------------------------------- simonc's Profile: http://www.excelforum.com...

How do I turn OFF the GETPIVOTDATA function?
I often want to reference a cell value in a pivot table by simply clicking on the cell (rather than typing in the cell reference) usually to use in a separate formula. I could do this in previous versions of Excel. In the current version of Excel I get the GETPIVOTDATA function which includes absolute cell references which means I cannot drag the formula down. This is quite minor but also enfuriating. View / Toolbars / Pivot Table / Click on 'Generate PivotData' icon to toggle function on/off -- Regards Ken....................... Microsoft MVP - Excel ...

best return from WCF service to other .NET consumers?
We have a web application today in Framework 3.5. The application consists of a Data Access Layer, Business Logic Layer and a Front Layer of ASP.NET webpages. In the DAL, a number of typed datasets are defined, and it is these typed datasets that are used as data transfer objects throughout the application. Now we want to expose functionality, from the application, to other .NET systems, through a WCF service. What should we let the WCF service expose to other systems? Here are the alternatives we have talked about: 1. Expose the DataSets we already have (simply return the typed datas...

return last value>0
I want to have a cell at the bottom of list named "current value" that returns the last value in a column that is greater than 0. What function would I use? Thank you. Hi Shawn one way: try the following array formula (entered with CTRL+SHIFT+ENTER): =INDEX(A1:A999,MAX(IF(A1:A999>0,ROW(A1:A999),0))) note: this won't work if there are also text entries in your range! Frank shawn modersohn wrote: > I want to have a cell at the bottom of list named "current value" that > returns the last value in a column that is greater than 0. What > function would I ...