Search & replace in formulae

Is it possible to do a search and replace which includes 
the cell formula contents ? I have a workbook that has 
cells which link to the contents of a cell in another 
workbook and I'd like to know if the naming has to remain 
constant or if I can replace a part of the name 
periodically. ie each year.

thanks
0
iain806 (2)
9/9/2004 3:34:40 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
971 Views

Similar Articles

[PageSpeed] 41

"Iain Rhodes" <iain@pricejam.com> wrote in message
news:8ec201c49682$858c5d20$a501280a@phx.gbl...
> Is it possible to do a search and replace which includes
> the cell formula contents ? I have a workbook that has
> cells which link to the contents of a cell in another
> workbook and I'd like to know if the naming has to remain
> constant or if I can replace a part of the name
> periodically. ie each year.
>
> thanks

In answer to the first part, yes you can search and replace on formalae.

In answer to the second part, as long as the links are valid (ie there's
something to link with), it shouldn't be a problem.

Ian


0
me1 (409)
9/9/2004 3:41:40 PM
Reply:

Similar Artilces:

Active Column Ref In a Formula #2
My problem is summarised as follows: A1 to, say, G1 contains a list of numbers Formulas read: In A1 =A1 In B1 =Sum(A1:B1) In C1 =Sum(A1:C1) In D1 =Sum(A1:D1) etc... to =Sum(A1:G1) Is it possible to have a formula which can datermine the column reference of the cell containing the formula? Some common element that replaces B1, C1, D1 etc. in the above example? Thank you If you insert: =SUM(INDIRECT("A1:" & SUBSTITUTE((LEFT(ADDRESS(1,COLUMN()-1),3)),"$","") & "1")) you will get the sum of the values from A1 thru just befor...

help me with excel formula please
A B can somebody help with a formula in the B colunm so that it 1 1 give values added upwards in the A colunm 2 (1+2) 3 (1+2+3) 4 ( 1+2+3+4) advance thanks In B1 enter: =A1 In B2 enter: =B1+A2 and copy down -- Gary''s Student - gsnu200903 "shaanu" wrote: > A B can somebody help with a formula in the B colunm so > that it > 1 1 give values added upwards in the A colunm > 2 (1+2) > 3 (1+2+3) > 4 ( 1+2+3+4) > ...

UDF to Convert formula results to text
Can a UDF be used to convert formula results from column A to text in column B? I know that I can manually cut and paste "special" to achieve this, but I want to avoid a manual step. Instead of a UDF use a macro. Change columns(2) to columns(1) to change the column withOUT the need for an additional column. Sub converttovalues() Columns(2).Value = Columns(1).Value End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Don" <don87109@hotmail.com> wrote in message news:%230kPQ8gDKHA.4608@TK2MSFTNGP02.phx.gbl... > Can a UD...

Beginner(formulas?)
Hey..im doing this add math project of mine and im really new to microsoft excel. Lets say i have the numbers 1 - 10 in column A, and i have to count them using the sum 4.2+2X (X representing the respective numbers in A). WHat formula do i have to use? and how do i duplicate the same formula for each of these numbers. eg: A B 1 4.2+2(1) = 6.2 2 8.2 3 10.2 4 12.2 5 14.2 6 16.2 7 18.2 8 20.2 9 22.2 10 24.2 the thing is, the question requires me to do the same thing for a hundred numbers..and its a tougher sum..so i need help..thanx -- nomad31 --------------------------...

replace dashes
I would like to delete the dashes in a numeric string but keep the numbers intact. ex 0777-3333-5 to 077733335 Jose, Edit | Replace replace dash with nothing. Be careful: If you only select one cell while enacting this command, XL will search your entire worksheet. If you want just one cell, choose the one cell and a blank cell. Regards, Kevin "jose" <anonymous@discussions.microsoft.com> wrote in message news:A77A42CA-7D41-4F47-8001-38BE5BEFE61B@microsoft.com... > I would like to delete the dashes in a numeric string but keep the numbers intact. ex 0777-3333-5 to 0...

Search Engine in an Excel SpreadSheet
Is there any way to include a search engine in a spreadsheet? I would like the user to enter a word into a cell and maybe click on a button to search the workbook much in the way a web search works? Thanks in advance Gary Try this. Right click sheet tab>view code>copy/paste this>SAVE>type search text into cell a1 of that sheet. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "$A$1" Then Exit Sub For Each ws In Worksheets With ws.Cells Set c = .Find(Target, LookAt:=xlWhole) If Not c Is Nothing Then firstaddress = c....

searching in non-adjacent cells
there is a column with values like 23 67 1 89 34 56 now the min() would give me 1 . I discard 1 and I want to search fo the smallest value in the remaining values. how do I do this? Please suggest a formula regards, betz -- betz ----------------------------------------------------------------------- betzi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1508 View this thread: http://www.excelforum.com/showthread.php?threadid=26718 Hi what is the logic to skip values=? do you want to skip only '1' -- Regards Frank Kabel Frankfurt, Germany "be...

How do I find document by searching standard properties in Word 20
For 7 years I have been able to locate correspondence in Word 2000 by searching for files where properties contain certain keywords, or where the files text contains specific words. I know how to add properties to a Word 2007 file but cannot find out how to search for a particular file by using those same properties, or by specifying specific text/words used in the file itself. The MS help screens say you can search by property values, but do not make is easy to find out how this can be done. The easiest way I've found is by using the All Files option in the older-style W...

help with a formula #5
I have 98 sets of magazines each set has 12 in the set what formula can I use to get a total of issues in all sets have you tried 98*12 -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Just Me" <no@isp.com> wrote in message news:eZ66ZEGHGHA.1192@TK2MSFTNGP11.phx.gbl... >I have 98 sets of magazines each set has 12 in the set > > what formula can I use to get a total of issues in all sets > =98*12 or =A1*A2 if the numbers are in A1 and A2 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Just Me" <n...

I have a formula in my workbook that was functioning but it doesn.
I have a simple formula that adds amounts. It was working all the time but now suddenly it's not adding on, i.e. the formula is still in the field but the result is not coming...... Can somebody help? Hi Ivan it could be that your numbers are formatted as text - copy a blank cell from another worksheet select your numbers and the formula and choose edit / paste special - add then select the formula cell press F2 and then enter alternatively, check that calculation is not set to manual - tools / options / calculation - ensure that automatic is selected Cheers JulieD "Ivan&qu...

Cell searching and Replacing
Hi Could use some help here please have a movie list someone sent to me and it has cells like Age of Innocence, The Bone Snatcher, The Etc Etc I would like to do a Next procedure and Replace the text , The and put the The in Front of the text So it is like this The Age Of Innocence The Bone Snatcher Etc Etc Hope someone can be kind enough to help me Regards Graham Try =IF(RIGHT(A1,5)=", The",RIGHT(A1,3)&" "&LEFT(A1,LEN(A1)-5),A1) Hope it helps. Graham Feeley Wrote: > Hi Cou...

Replacement for Calendar/Contacts Public Folders?
I own a Public Folders in our Exchange Server environment. Among other things, we have a folder of Contacts (customers) and a Calendar folder (vacations). I have been told by our Exchange IT people that they are developing a migration plan away from Public Folders since Microsoft keeps saying that they're going to drop them in some version of Exchange some day. So, my question is this: what is the replacement strategy for Contact and Calendar folders that integrated equally well with Outlook? Can SharePoint do this as well or better? It took a LONG time to get people to use the stu...

time formula question
This formula works great if the ending time is before 0:00. =IF(D21>0,B21/INDIRECT("Sheet1!E"&MAX((A21>=$C$9:$C$11)*(A21<=$E$9:$E$11)* {2;3;4})),"") $C$9:$C$11 is my starting time i.e. 20:00 $E$9:$E$11 is my ending time i.e. 04:30 How can I get this to work if A21 = 20:15? TIA, David Try this: =IF(D21>0,B21/INDIRECT("Sheet1!E"&MAX((($C$9:$C$11<$E$9:$E$11)*(A21>=$C$9:$C$11)*(A21<=$E$9:$E$11)+(($C$9:$C$11>=$E$9:$E$11)*((A21>=$C$9:$C$11)+(A21<=$E$9:$E$11)))*{2;3;4})),"") HTH -- AP "David" <dfizer@r...

Simple Search and Replace Question
Hi. I am searching and replacing various characters in an Excel column. I need to delete all the asterisks (*) in this column. How do I do this? That is, how do I make Excel "think" the asterisk is NOT a wildcard characteer but a real character that I want to get rid of? Rebecca wrote: > Hi. I am searching and replacing various characters in an Excel column. I > need to delete all the asterisks (*) in this column. How do I do this? That > is, how do I make Excel "think" the asterisk is NOT a wildcard characteer but > a real character that I want t...

Help with Formula
Hi, I'm trying to count dates in a range in another worksheet (Test 1), count is to be based on being euqal (=) to or less than (>) the date that is entered in B10. If there is no date in B10 I want the cell to remain blank. The data in 'Tests 1'!AD3:AD1000 is entered and formatted as a date as is the data in B10 I am using: =IF(B10="","",(COUNTIF('Tests 1'!AD3:AD1000,"=>B10"))) this only returns 0 Suggestions welcome. -- Steve Reality is the leading cause of stress amongst those in touch with it. - Jane Wagner =IF(B10=&quo...

Linking to files with a formula
In Excel 2003 what is the trick to linking to a closed work sheet using a formula? I'd like to put a list of file names in column A then use a formula to ling to cells from those books. Straight link works ='C:\My Docs\[File_1.xls]Sheet1'!X1 Formula does not ='C:\My Docs\[&A4&]Sheet1'!X1 Indirect will not work because I have too many books. Grateful for any help on this one! Thanks! xjvs Hi JVS Maybe you build your formulas with a macro I use getopenfilename here but it is also possible to loop through the column with file names http://www.rondebruin.nl/s...

Want to Change Range Selected in Formula
I would like to pass a variable through a function. Based on the value, I have some ranges already name ie. Dataquestion#. I would like to be able to change the named range to use in this formula based on numbers, such as mQNo. Any help would be appreciated. mQNo = 1 Set mQuestionRange1 = Worksheets("Data").Range(Chr(34) & "DataQuestion" & mQNo & Chr(34)) This is siomple. I don't think you understadn strings. You don't need a second set of double quotes. It is that simple for example Var1 = "abc" Var2 = "def&q...

Enter Formula without using =
I would like to be able to enter a formula into a cell and have it not require me to first type the equals sign. For example, when I type 12+12 I would like for the cell to return 24 and when I edit the cell, it would show =12+12 (e.g. Excel automatically inserts the = before the formula) Does anyone know how to do this? Tools>options>transition and select transition formula entry -- Regards, Peo Sjoblom "Brian Tankersley" <bftcpa@gmail.com> wrote in message news:6A70362C-6A75-4C5B-A50A-ECA311B48DA4@microsoft.com... >I would like to be able to en...

Inter sheet formula won't format properly
Hi MS Excel 2003. I am writing a formula with a reference to a cell in another sheet, and Excel is behaving oddly. Sometimes it will give me the result, ie what is in the target cell, but sometimes it gives me the formula. The formula I am writing is simply ='2'!A22 thereby trying to get cell A22 from the sheet called 2. Why is Excel not always just giving me what's in the target cell? Sometimes all I can see is ='2'!A22. It's driving me nuts. I have looked at all the formatting options with no luck; I have toggled (using Ctrl`) the formula view with the norma...

Search
I was wondering where does IE 7 & IE 8 keep search provider info? If I wanted to set a default for all machines in a domain without having to touch each machine, is this something I could script or put in a batch file? Or is there an active directory setting? Thanks. Start here: Internet Explorer Administration Kit - IEAK 8, IEAK 7, IEAK 6 | TechNet: http://technet.microsoft.com/en-us/ie/bb219517.aspx Group Policy Settings Reference for Windows Internet Explorer 8: http://www.microsoft.com/downloads/details.aspx?FamilyID=ab4655f2-0a3c-42eb-974d-24b2790bf592&display...

Search criteria between
I have a table containing let´s say 5 posts – A,B,C,D and E. Then I make a selecting question with query conditions like “Between C and H” The result then will be C,D and E. If I want to make a report from this result and I in the header of this report want to have “This report contains posts between C and H” How will I achive this ??? -- Thanks in advance for Your help The usual way to give the user a way to select criteria is by unbound controls on a form. In your case you need a way for the user to select C and H. You could use a form named FrmCriteria with two co...

Creating a conditional formula to increment #s in a test script
Greetings, I have an Excel question. I am creating a test script template for our team and I an in need of some formula help. Here is what I wish to do: Let's say I have a column A in my test script. I wish to label this column with my test script # TC1 TC2 TC3...(and so on) However, the problem is I have section breaks in my test script that divides the document into sections: eg.. <LOGIN SECTION> TC1 TC2 TC3 <LOGOUT SECTION> TC4 TC5 TC6 Now the problem is that if I want to add a new test case to the "Login section" above, I need ...

Don't know which formula to use!
If I have several sheets in a workbook, and I want one as the master to refer to all others so that when a number is placed in a cell on the master it is either subtracted or added from the same cell in another sheet, depending which sheet or sheets are named from the master sheet. Is this possible? Could you explain what you mean in a bit more detail? For instance, will the entry on the Master sheet be made in the same cell each time? If not, will it be the same cell for each named sheet? How will the name in the master sheet correspond to the value- to the left? right? above? below? Ho...

Please help with formula #2
It has been a long time since I've needed to write for help, but I need it now. I had a friend come to me for help with a spreadsheet he's creating for the food service company he works for. While I taught advanced Excel in college, that was four years ago, and I don't recall this particular issue. The first worksheet is a payment summary sheet. Next, there are sheets for (invoices) for each organization the food service company deals with. On each invoice sheet, there are cells which identify information common to each of these company sheets. All of the invoice sheets are t...

Data row wise, formula column wise
I have data in cells b2:aj2 I want to refer to these cells in a column from a2 down. I can manually type in: A2: =B2 A3: =C2 A4: =D2 Rather than doing so, is there a formula I can type in once in A2 then copy down? -- Regards, Fred If you just need the data in column A........ Select B2:AJ2 and copy. Select A2 and Paste Special>Transpose>OK>Esc Gord Dibben Excel MVP On Wed, 7 Dec 2005 17:18:55 -0600, "Fred Smith" <fredsmith99@yahoo.com> wrote: >I have data in cells b2:aj2 > >I want to refer to these cells in a column from a2 down. I can manuall...