Locate a cell, based on a criteria, then use the 'Cell' command...

I am using a pivot table to configure some data.  In the first column, the 
pivot table of course displays only one instance of the text representing of 
data group.  What I need to do is determine in which cell a particular item 
of text (i.e. the criteria) resides in.  Once I can locate the cell where my 
'criteria' text string resides, I can then use the CELL function to return 
the row number.

My question is...  what excel formula can I use to locate a cell in a single 
column range, where the cell contains the text = "XYZ"

My eventual plan is to then use the following to return the row number where 
that text string reside:  =CELL("Row",'cell found using above formula')

Thanks for the help!

Craig
0
cdavidson (21)
11/17/2005 5:45:05 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
586 Views

Similar Articles

[PageSpeed] 0

Turns out basic use of the 'Match' function was all I needed.

Thanks anyway!

"cdavidson" wrote:

> I am using a pivot table to configure some data.  In the first column, the 
> pivot table of course displays only one instance of the text representing of 
> data group.  What I need to do is determine in which cell a particular item 
> of text (i.e. the criteria) resides in.  Once I can locate the cell where my 
> 'criteria' text string resides, I can then use the CELL function to return 
> the row number.
> 
> My question is...  what excel formula can I use to locate a cell in a single 
> column range, where the cell contains the text = "XYZ"
> 
> My eventual plan is to then use the following to return the row number where 
> that text string reside:  =CELL("Row",'cell found using above formula')
> 
> Thanks for the help!
> 
> Craig
0
cdavidson (21)
11/17/2005 6:30:06 PM
Reply:

Similar Artilces:

how much is too much data for a cell?
a cell should hold 32,000 characters if i am not mistaken. i have a large spreadsheet that has codes, their use, description and the # of times they have been 'hit'. most of the information fits in a cell very easily but i have one description that is huge. i made the cell 255x409 which is the max and what happened was part of the information showed and then the rest of the cell was blank. at that cell size there was more than enough room for all the data but for some reason it would not all show. i tried things like changing the alignment and it is on wrap text. i tried selectin...

Hide Field based on Another
Hi, I have a field call "Status". The content can be "Active" or "Non-Active". If it is set to Non-Active, I want another field call "Reason" to be displayed so the user can put an answer in that field, otherwise "Reason" is not displayed. How can I do this in my form? Thanks very much. On Mon, 30 Apr 2007 08:32:02 -0700, Ty Archer <TyArcher@discussions.microsoft.com> wrote: >Hi, > >I have a field call "Status". The content can be "Active" or "Non-Active". >If it is set to Non-Active, I wan...

Using ^F (Find) in the VS
Having long exposure to *NIX systems and tools, I think there's a language barrier between me and the Find Dialog in the Visual Studio. I wanted to search for an occurrence of an equal sign followed by a tab-character in the current file. If this were, say, the nedit-editor in Linux, I'd check the "regular expression" box in the dialog and search for the string '=\t' (without single quotes). Doing this in VS finds nothing. searching for a tab with '\t' just finds the occurrence of the 't' characters. That's not what wanted. I tried to enter a ...

Clear a range of cells given 2 Cell addresses
Hi I want to be a able to clear a rectangular area. I know the top most left corner address. I know the bottom righ corner address. all addresses are in the format r,c where r,c are integers. So I know cell 1 to have coordinates (r1,c1) and cell 2 to have coordinates (r2,c2). How can I issue a clear command to clear all data and formulas in this region? Thanks much in advance. Ekareem Hi Ekareem, Try ... range(cells(r1,c1), cells(r2,c2)).clearcontents. With kind regards, JP "ekareem" <ekareem@discussions.microsoft.com> wrote in message news:...

Inseting data in cells
I have a row of cells that have last names in them, is there a way I can insert some data into all of the cells, this data will be the same for all names. Ex Before jones1 After (alias=jones1) then if possible export all of the names to a word or text file all in one line. Ex (alias=jones1)(alias=smith2)(alias=johnson3) Thanks for any help!!!! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Assuming last names are in A2:Z2 Put in A3 :...

Update attribute using ldp
Hi There, I have written an ldf script which creates few custom attributes and an auxilliary class which contains these custom attributes. Also this auxilliary class is part of Users and OrganizationalUnit classes. Now I want to update the values to the custom attributes. How do I do it using ldp? I can set values for the inbuilt attrbutes without any issues, however when I try to set the values for the custom attributes I get below error, Error 0x20B5 The name reference is invalid. Please advise. Thanks, Suresh -- sureshpalani ----------------------------...

Using User input in excel VBA
I'm supposed to make an invoice of 4 items. Each item needs to have user input for the description, price, and quantity ordered. Then I need to calculate the cost of the 4 items. Then calculate the sales tax at 6%. Then calculate the total cost including sales tax. This needs to be done using VBA code in excel. --I'm curious as to how to prompt the user to give me information. --I'm curious as to how I would use the users input in another cell once they entered it. -- I'm curious also as to how VB will know that something is entered once the user enters something into t...

how to protect formulas in a cell
Hi everyone. You have cell where values are entered, but it has a formula in it. How can I protect the formula and still let the user enter data in it? Or How do I RESET the table to its orginal state. Say all cells should be set to "o" or "empty" and everything, formulas, formatting for the entire table goes back to when first opened. Please see Pic in attachement (having problems with my server uploading the file). Pls let me know if get to see the pic, I am learning... TIA Enrique +-------------------------------------------------------------------+ |Filename: ...

Chart Location
Hi, How to specify the location of a chart in a sheet (The sheet contains 4 charts).The chart location should vary dynamically depending on the values associated with it. Thanks and Regards, Sumathi Sumathi - You need to specify the coordinates and dimensions of the chart's parent chart object, in pixels. With ActiveChart.Parent '' or With ActiveSheet.ChartObjects(1) .Top = 50 .Left = 50 .Height = 175 .Width = 225 End With - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTe...

Which CD Keys have I used?
I have the Student version of Office, which allows me to install on three machines via 3 unique cd codes. I have already installed on 2 machines (a few months ago) and am now adding a third, but I don't recall which cd codes I've already used. The machines will be networked, so I need to get it right. Is there any way to figure out which codes I used? A previous thread ("Matching Office ID to certifiacte of authenticity") from a few weeks ago seems to say no, but I'm not sure it is talking about the same issue. Thanks for any guidance. In article <C030F408.529F%...

Convert Number Field to Date using query
I have search the forum and found several people asking this question, but my situation is a little different. I have a database that I need to convert a number field to a date, but the number fields have the date as "08/07/2007". I have not been able to find a way to convert this number field to a date. Any suggestion/comments would be greatly appreciated! Thanks You sure it's a numeric field? Numeric fields wouldn't have slashes in them. If it's text, you can use the CDate function. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please...

DPM 2010 RC co-locate SQL Server
I'm creating a protection group for SQL Server 2005 SP3 (CU8) with DPM 2010 RC. I have about 15-20 small databases and about 10-15 larger databases. When I create the protection group the option to co-locate is checked (and not gray), but when the protection group is created I end up with many 10gb disk partitions. Isn't co-location supposed to prevent that? I read that the default for co-location is 30 databases (how do I increase that?) so I tried hand picking the larger databases from the list rather than the auto option and only using those in my protection group....

Location Location Location? No, Traffic Traffic Traffic! #9
Do you want a great backend product that is selling like crazy this week? My readers are running to get this NEW CONCEPT! I�m selling more this week than I ever did in the past months with any other program. My commissions are climbing very fast. I�m really excited! This is the best ad copy I�ve read in months! It sells. If you haven't felt the shockwave yet, stop everything you are doing and read this to be one of the first promoters. The inner circles of the shockwave are raking in the profits like fury. This will pay us for years to come as it is the first �Wave� of the NEW I...

Count the Colorindex of a cell with a conditional format
Is there any way (with or without code) to count the number of cells that have a condition that displays a specific colorindex tied to a conditional format? Thanks, Barb Reinhardt Barb, I answered no to this once and Bob Philips corrected me and pointed me towards his page below. You can count conditionally formatted cells but it ain't easy. http://www.xldynamic.com/source/xld.CFConditions.html -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the questio...

Location Availability
Can anyone tell me if it is possible to setup a location or user for a location so that when people schedule meetings they can see if the area they want to hold the meeting in is available. For example we have a meeting room and a showroom, so I want people to be able to check the availability of these rooms directly in outlook when creating a meeting request. Thank you. I've seen it done with Outlook -- JoAnn Paules MVP Microsoft [Publisher] "Tim" <Myemail@here.com> wrote in message news:11n7egikgheercc@corp.supernews.com... > Can anyone tell me if it i...

why the toolbar wont be dockable after use SetButtonInfo?
i use the code like the following: m_wndToolBar.SetButtonInfo(0, IDC_WRITEMSG, TBSTYLE_BUTTON, 0); m_wndToolBar.EnableDocking(CBRS_ALIGN_ANY); EnableDocking(CBRS_ALIGN_ANY); DockControlBar(&m_wndToolBar); ...

cell contents automatically populate another cell
I'm working on a timesheet - 2 weeks worth of dates. The last date listed in the list (B31) I'd also list to have listed in F4 which is where my "Pay Period Ending Date:" is to be. I've been manually adding the date there but thought why should I have to look that date up all the time when it would be listed on my sheet after I get the dates put in for the 2 week pay period. I'm sure it's an easy thing but I haven't been able to find my answer thru excel help. Thank you! Guess I figured it out -- I just typed = in F4 and then went to click on B31 to ...

Count If / color criteria
Is there a way to use count if (or some other formula) to count the only the number of "x"s in a collumn that are red (or count the number of cells in a collum that are highlighted with a different color). TIA for any help. G There's nothing built into Excel that lets you count by colour. Chip Pearson has some User Defined Functions that you can use: http://www.cpearson.com/excel/colors.htm G. wrote: > Is there a way to use count if (or some other formula) to count the only the > number of "x"s in a collumn that are red (or count the number of cells in a ...

Can I do without using VSTO
Hi all, Can I do code against Excel objects without using VSTO? Clara -- thank you so much for your help You can manipulte Excels objects with code using the VBE. If you're wanting to create a truly secure COM add-in then VSTO is the way to go. If you're only wanting to create a .xla add-in you can use the VBE that accompinies excel. "clara" wrote: > Hi all, > > Can I do code against Excel objects without using VSTO? > > Clara > -- > thank you so much for your help Yes, you can use VBA, VB6 via COM, C/C++ via the C API a...

Using Access w/Word Merge (Access 2002)
I inserted a column in a data table for expiration date. While writing a merge letter where I wanted to insert that information I discovered it didn't show up in the Merge Fields. Should I have instead renamed an existing column (field) and moved it where I wanted it on the data table? I deleted all extraneous column/fields, so I don't have an empty field to utilize. Must I create a new database or is there a way to identify this new column as a data field? How are you getting the data into your merge document? Query from Word, Access query, VBA? Steve "Helen" w...

Recognize Cell Color and Sum Data accordingly
I've tried SUMIFS to recognize cell colors and add the numbers (which are hours worked in the day) in the corresponding row. The kicker is the yellow cell doesn't necessarily have the same hours and I can't get Excel to recognize colors only for one of my criteria. It could range from 2 to 12 hours for any given day. The row may contain data with up to five different colors associated depending on the kind of work performed that day. So let's say I want to pull all yellow cells for a given time period and it looks something like the following. A ...

wrap cell
here is problem: I have two cells with setup wrap propertie, both cell has equaly number of characters problem is when cell A2 is filled with text longer than half of page it move to next (second) page leaving first page half empty (filled with text at A1 cell). Is there any way to break wrapped cell ? thanks! Zikel -- zikel ------------------------------------------------------------------------ zikel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24887 View this thread: http://www.excelforum.com/showthread.php?threadid=384258 Only be cutting some text fr...

Setting a Cell's Value Based on Another Cells Contents
I can't imagine that this is as hard as I'm making it... but admittedl I am having some trouble, so here's my post....... I have two columns, the first (D) is titled Job Specialty Area an contains a dropdown list of 30 job types including one for "Other Jo Specialty Area". The other column is (E) and titled "Or Other..." , an allows the user to type in their own job specialty area (instead o choosing one from the list). The goal, if a user starts typing in column E, to default column D t "Other Job Specialty Area...." ANY ideas, comments, suggestio...

Programming PowerPoint using VBA
I have been working on a project where I need to add some shapes to a presentation using VBA. Of course I run the code as I test many times and each time I do notice the shapes just keeping getting added to the slides. What I don't understand is why the objects I am adding persist after the slide show is no longer executing. They even remain part of the presentation when I close and reopen it. Why is that? I haven't even saved the presentation. I know I am missing something fundamental here, but it has been difficult to find a resource on the Net to explain away my co...

How do I change an org chart shape after using the provided stenci
Hello, After running the Visio Org Chart stencil (wizard), I can't seem to change shapes. For example, changing a 'manager' shape from a rectangle to a circle. I would like to be able to select multiple existing shapes (rectangles) and change them all to circles, while retaining the connections between them. Any help would be much appreciated. -- JG You can only change easily between the pre-defined shapes and themes. Any more drastic changes would require you to edit the Master(s) "JG" <JG@discussions.microsoft.com> wrote in message news:7643AB4B-763B-...