find specific titles in a table

Hi,
I am looking for a certain text in a particular spread sheet,what
function/code can I use and how can I store the position
Thanks
0
7/29/2009 6:44:44 PM
excel 39879 articles. 2 followers. Follow

3 Replies
505 Views

Similar Articles

[PageSpeed] 59

Hi,

why not give us a little more detail:  is the text going to appear more than 
once.  Are you searching for a particular item of text or will there be many 
items.  Do you want its position in R1C1 notation, as an offset from a 
specific location, in A1 style and with its value.  And is the text imbedded 
in a longer string?

suppose the range is B1:G100 and you are looking for the word Apple by 
itself in one single cell, then you can use the following array formula:

=ADDRESS(MAX((B1:G100="Apple")*ROW(B1:G100)),MAX((B1:G100="Apple")*COLUMN(B1:G100)))

Array - to enter it you press Shift+Ctrl+Enter, not Enter

-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"layla" wrote:

> Hi,
> I am looking for a certain text in a particular spread sheet,what
> function/code can I use and how can I store the position
> Thanks
> 
0
7/29/2009 11:42:01 PM
Thank you Shane,for your reply,however your code doesnot run.I am
trying to find the bug but since  i am very new to VBA ,I have trouble
to undrestand things that  that you put inside the brackets

Thanks!
0
8/6/2009 3:44:36 PM
Shane's posting was a cell formula.........not VBA


Gord Dibben  MS Excel MVP

On Thu, 6 Aug 2009 08:44:36 -0700 (PDT), layla <princess.layla2@gmail.com>
wrote:

>Thank you Shane,for your reply,however your code doesnot run.I am
>trying to find the bug but since  i am very new to VBA ,I have trouble
>to undrestand things that  that you put inside the brackets
>
>Thanks!

0
Gord
8/6/2009 5:26:35 PM
Reply:

Similar Artilces:

How do I find and replace part of a hyperlink that isn't visible?
I would like to use Excel 2003 Find and Replace to change all the hyperlinks in a worksheet or workbook to point to a different pathname. For instance part of the hyperlink path contains "iis" and I would like it to be changed to "iis2" without changing the rest of the hyperlink. You have one response at your first post. Dede wrote: > > I would like to use Excel 2003 Find and Replace to change all the hyperlinks > in a worksheet or workbook to point to a different pathname. For instance > part of the hyperlink path contains "iis" and I would li...

find and replace #12
I am trying to do a find and replace on formulas that are linked to a specific tab and then replace the name of one tab with another, and it can't seem to recognize the name. The tabs have date name with a yyyymm format so I need to change '200110 to 200112' to '200201 to 200203' I can't seem to figure out why the simple replace doesn't work. Any thoughts or suggestions??? Thanks, Tiffany Maybe in one place your "numbers" are "numbers" and in the other they are actually "text" that just looks like numbers.......... Vaya con...

Changing two way tables into one way tables
Hello - I need to be able to 'undo' a two way table. The data is in this format: Student number English Grade Maths Grade US456 A B+ US455 A- C+ And I need it to look like this: US456 English Grade A US456 Maths Grade B+ US455 English Grade A- US455 Maths Grade C+ So each grade has a separate row - other than copying and pasting hundreds of records is there a simple way to do this? =A2&" "&$B$1&" "&B2 where : A2 = "First Student in your table"; $B$1="English Grade" and B2="English grade for first student and drag th...

Find Distribution Lists that a user is the owner
Hello, Is there a quick way to list out all the Distribution Lists that a particular user is the owner? When we have users leave the company, we constantly get requests to transfer ownership. Thanks, Sun Sun <Sun@discussions.microsoft.com> wrote: >Is there a quick way to list out all the Distribution Lists that a >particular user is the owner? When we have users leave the company, we >constantly get requests to transfer ownership. Not without writing a small script to query the AD. And, if the user account is deleted from the AD before you ask the question, you wo...

Pasting table from Word to Excel keeping rows the same
When I paste a table from Word to Excell the data that appears on line 2 in Word appears on line 7. I cannot pull the file up to line 2 because it is hidden. Any suggestions? ...

How do I store calculations from a form to a database table?
My boss and I are stuck trying to figure out how to make our calculations be stored in our database table. Currently our calculations are in the design view of the form. We are able to see the anwer to the calculations, but they are not stored in the database table. What are we doing incorrectly? Is there some other place or way that we are supposed to do calculations? Thank you for your help. The short answer is - don't. In most cases you should not try to store calculated values in a table. Calculations should be done in queries or in unbound controls on forms. Storing the value ...

Imiporting Table Definitions
BlankMy application is split into a front end logic and back end data database. This allowed my client to start using the application while I continued development. Now I have new features whose implementation required the addition of additional columns in several existing tables. My forms work against the new table definitions in my test data base. Now I need to deploy the updated application, but I'm faced with making a lot of changes to the production data base introducing the probability of mistakes. I'm looking for an automated way (read less risky) to import the modified tab...

flattening aggregated fields in pivot table
I'm trying to build what *should* be a simple pivot table with 2 fields and having some difficulty. The problem is that Excel wants to automatically aggregate like values. For instance, the two fields are Product Family and Part Number. There are cases where 2 different Product Families can share a Part Number (don't ask). In these few instances, The pivot table wizard automatically groups the 2 instances and only lists the Part Number once; in the second instance, it generates a blank cell where the PN should go. This wreaks havoc on one of my macros that needs every singl...

To get the same header,footer and sheet titles across multiple sheets of a workbook.
Dear Friends, I want a workbook to have the same header (as some cell value of a sheet in workbook) and similarly same titles (rows and columns titles) in different sheets. I reached to the following code. It worked well for the header (and also footer) but it didn't work for the rows to repeat at top or columns to repeat. It only works for the active sheet. How can i make my work done?? The main problem i think is to make ActiveSheet.PageSetup.PrintTitleRows work across multiple sheets. Please help me. I have the code attached here for yur reference. 'this is the workbook c...

Make over �1000 by investing only �5 FIND OUT HOW HERE WONT HURT TO LOOK
Make Money with PayPal by "beatlerockin" <beatlerockin@[EMAIL PROTECTED] > May 11, 2004 at 11:54 AM Follow the directions below and in two weeks you'll have up to $2000.00 in your PayPal account. There is a very high rate of participation in the program because of its low investment and high rate of return. Just $5.00 to one person! THAT'S ALL !!! If you are a skeptic and don't think the program will work, I urge you to give it a try anyway! It REALLY WORKS! Why do you think so many people are promoting this? LOOK AT IT THIS WAY If the Program is a total fail...

Deconstructing a table
Hi, I have a table with column A populated with identifiers and a dynamic number of cols to the right storing the weights of different products. For example say columns B - M. I would like to un-build the table such that the result is a long list of three columns. Col A = Value of ID at row(i) Col B = Value of Header (product name) Col C = Value of weight at row(i) Col A | Col B | Col C A2 B1 B2 A3 B1 B3 A4 B1 B4 A2 C1 C2 A3 C1 C3 A4 C1 C4 etc... I was th...

Autofill formulas when adding rows in tables
Is it possible to have the formulas you are using in a column automatically appear in the new cell in the column when you add a new row? I swear I saw this demonstrated in a video but they did not explain how to do it. I just converted a table of data to an Excel Table (using Format as Table command); took out an old forumla (at top cell of data in column) and then reentered in strcutured reference. I then copied down the entire column. But when I add a new row to the table that new cell in that column does not have the formula. Did I imagine that video or am I missing a st...

showing repeating values in the rows of a pivot table
The default setting in pivot tables results in showing the repeating value of a row header in the first row only. Is it possible to allow the repeating value to populate all the cells to which it applies? Not while it's still a pivottable. I'll usually convert to values (or make a copy of that pivottable and convert that copy to values). Then I'll use techniques like those at Debra Dalgleish's site to fill those empty cells: http://www.contextures.com/xlDataEntry02.html kdschreck wrote: > > The default setting in pivot tables results in showing the repeating valu...

Edit : Find....not working between sheets
Hello, I have created a workbook that shows supplies for 20 differen cabinets. Each cabinet is listed on a worksheet. When I want to fin a particular item, I select all the sheets and go to Edit, Find an type in the item and it takes me to the correct cabinet. The problem am currently running into, is that if there are the same item i multiple cabinets, it just goes to the last one, instead of starting a the first one and continuing forth. So....If I have gloves of differen types in cabinet 1, 4, and 7 and perform this function, it should tak me to 1 and then when I click on Next, it s...

How do I get UML profile on Visio 2003, I can't seem to find it?
My laptop has visio 2003 installed on it, but I can't find the UML profile, so I can do activity diagrams, class diagrams etc. I followed the instructions in online help http://office.microsoft.com/en-au/visio/HP815502391033.aspx? pid=CH010267621033, but that option doesn't come up, must I install the component? Thanks Andy Maybe you only have Visio Standard, not Visio Professional? http://www.qbssoftware.com/DDGzone/product_brochure.asp?product=MSVISIO#fea "Andius" <Andius@discussions.microsoft.com> wrote in message news:7ABAB5D0-409A-431B-9A46-AAB733A97A42@mi...

Extract Data from table for Mailing Lables
I have a database that I import into Access XP, it is a tab delimited file that is imported into an access table, let's call it DATA_TABLE. It is in the format: Name HairColor EyeColor ShoeSize ShirtSize Bill Brown Brown 9.5 M Tom Brown Blue 10.5 XL Jack Black Brown 8.0 M Ed Blonde Blue 11.0 XXL . . . What I need to do is extract certain elements from the table in order to make mailing labels. For example, I want to make mailing lables to only those that have an ...

finding unique numbers
I have a column of numbers. Some numbers may appear in the column more than one time. I need to find out the numbers that appear only one time. For instance the column looks like this: 32 47 15 92 32 15 92 I need to find out that 47 is only used once. How can I do this? Thanks. Assuming that A1:A7 contains your data, try... B1, copied down: =IF(ROWS($B$1:B1)<=SUM(--(COUNTIF($A$1:$A$7,$A$1:$A$7)=1)),INDEX($A$1:$A$ 7,SMALL(IF(COUNTIF($A$1:$A$7,$A$1:$A$7)=1,ROW($A$1:$A$7)-ROW($A$1)+1),ROW S($B$1:B1))),"") ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this...

How to find data in a wave file ... ? ... ?
Hi everyone, How are you ? Happy new year for all !!! Well I want to know how can I find some data like a tone in a recorded wave file in WindowsPCM format in 11kHz - 8bits. This file(s) will contain some conversations like phone conversations and some tones in midlle so I need to find this tones, how can I read this datas and recognize some tone ? Very Thanks for helps, DaniloAlmeida wrote: > Hi everyone, > > How are you ? > > Happy new year for all !!! > > Well I want to know how can I find some data like a tone in a recorded wave > file in WindowsPCM form...

Cant find NEWS
I had the menu option "NEWS" on my standard toolbar so that I can go to the newsgroups. I attempted to move NEWS to another menu and lost it. I looked in every menu for it and I tried to put it back again but I cannot find it even when I go through the normal route of adding buttons, going to configuration and then to the GO Menu. Nowhere is there to find the NEWS menu choice. I even re-installed Outlook. Help!!! What version of Outlook? In Outlook 2003 right-click the toolbar, choose Customize. Go to the "GO" group and you'll find News about 2/3rds of the way dow...

MS Access Copy record to another table based on a form input 10-08-07
Hello. I have 6 Tables, all with identical fields. I want to copy a record from one table into another table, which table depends on what table is selected in a form. So if the table names are One, Two, Three, Four, Five, Six, and the record that I want to copy and insert into another table has been selected by a query in a form. I want the form to direct where to insert the table. So if the form has table Two selected (from the drop down list) then it should insert the record into table Two. If the form has table Four selected then it should insert it into table Four. The table the record is...

Cannot find program message
I have downloaded and installed Chaos Pro and when I go to the e-mail I recieved for a form, The message I get states that the computer needs to search the internet to find the program that is needed to down load form? I have been having this problem for a while. Some forms I can down load and copy on my copier and others have the missing program message. I go to MSN for a program I need but I can't figure out which program I need? Thanks JEFF This discussion group if for the database software Microsoft Office Access. To get help with Chaos Pro, post your question to a newsgroup for Ch...

Show file location in title bar?
Is there a way to make Excel or Word show the location of a file in the title bar. Right now, it just shows the name of the file. I was thinking in 2003, it showed the file location, or perhaps there was an option to show it? I can't find the same thing in 2007 and it would be extremely helpful to have it! I use template files with the same name (for easy searching) but save in different client folders, which is why I want it to display the full path. Otherwise, I wouldn't care. Thanks!! Add "Document Location" to the QAT. You'll find it in the "Com...

Adding total dollars based on specific data from another column
I have a cell I'm trying to calculate the total dollars based on specific criteria from a column of data. Example, column A has 25 cells with data like A, B, C, D, E, F, and a second column with dollar amounts associated with each of these outcomes, i.e. A=$5, B=$2, C=$9 etc. I want to create a formula in a different cell that tells me "if cell = A for $5 then what is the total dollar amount. A "if then" scenario....Any help???? -- Espo Hi! Maybe this: =SUMIF(A1:A25,"A",B1:B25) Biff "Espo" <Espo@discussions.microsoft.com> wrote in messa...

Creating multiple pivot table report at run time
Hi, I'm trying to automate the creation of a report through VBA and desperately need some help. I have a named range 'Countries' on sheet 1. On sheet 2 there is loads and loads of data related to each country and various cities within each country. The number of cities varies per country and the list of countries will change each time this report is run (source data will change). On sheet 3: A - rows 1 to 10 are static B - rows 11 to 26 are formulaic and lookup bits of information for country 1 C - row 27 is the top of a PivotTable1 extracting data for cities...

Help!!! Using WildCards for Find and Replace
Hi there, I am using PPT 2007. I am want to use WildCards function for finding and replacing a string. My problem is, many ppt decks I will get will have phone numbers as (xxx)-xxx-xxxx. I want to replace all those formats to +1 xxx xxx xxxx. (adding +1, removing brackets for first three digits, and replacing hyphens with spaces) In Word, we have an option of using Wild Cards while using Find and Replace option. But in PPT, I am unable to do the same function. I need search where it is used and to manually replace each. This is the command I use in Word: Find What : ...