#### Replace a portion of a formula

```Does anyone know of a way that I can replace a portion of a formula
throughout a worksheet without changing the other part?

I have been able to find the cells that need to be changed by searching for
=SUM(\$C?*\$E\$1)

I need to change the C to a D is all cells. Is this possible?

I'd appreciate any help! Thanks!
```
 0
Utf
11/23/2009 4:23:02 PM
excel.misc 78881 articles. 5 followers.

3 Replies
742 Views

Similar Articles

[PageSpeed] 29

```Find

SUM(\$C

and replace with

SUM(\$D

use the look in formula option.

HTH,
Bernie
MS Excel MVP

"krkward" <krkward@discussions.microsoft.com> wrote in message
news:25D2BAD8-646C-4C93-AD21-01B54DBB396E@microsoft.com...
> Does anyone know of a way that I can replace a portion of a formula
> throughout a worksheet without changing the other part?
>
> I have been able to find the cells that need to be changed by searching for
> =SUM(\$C?*\$E\$1)
>
> I need to change the C to a D is all cells. Is this possible?
>
> I'd appreciate any help! Thanks!

```
 0
Bernie
11/23/2009 4:36:08 PM
```Find
=sum(\$c

Replace with
=sum(\$D

If this post helps click Yes
---------------
Jacob Skaria

"krkward" wrote:

> Does anyone know of a way that I can replace a portion of a formula
> throughout a worksheet without changing the other part?
>
> I have been able to find the cells that need to be changed by searching for
> =SUM(\$C?*\$E\$1)
>
> I need to change the C to a D is all cells. Is this possible?
>
> I'd appreciate any help! Thanks!
```
 0
Utf
11/23/2009 4:40:06 PM
```You might also want to think about whether you needed the SUM function.  Was
there something that you wanted to add to \$C2*\$E\$1, for example, or was your
formula intended to have been just =\$C2*\$E\$1 ?

If you don't know what the SUM function does, look it up in Excel help.
--
David Biddulph

"krkward" <krkward@discussions.microsoft.com> wrote in message
news:25D2BAD8-646C-4C93-AD21-01B54DBB396E@microsoft.com...
> Does anyone know of a way that I can replace a portion of a formula
> throughout a worksheet without changing the other part?
>
> I have been able to find the cells that need to be changed by searching
> for
> =SUM(\$C?*\$E\$1)
>
> I need to change the C to a D is all cells. Is this possible?
>
> I'd appreciate any help! Thanks!

```
 0
David
11/23/2009 8:11:22 PM
 Reply:

Similar Artilces:

Formula help #27
I have four columns of data labeled "HOURS WORKED", "HOURLY WAGE" "REGULAR PAY", and "OVERTIME PAY". I can fudge the cells to get the correct answers but I need a constan formula, which can be copied to all the remaining cells in the column. For example, an employee works 50 hours and is paid \$4.00 an hour. need a formula that only calculates 40 hours at \$4.00 (REGULAR PAY) an a formula that calculates the remaining 10 hours at 150 % (OVERTIM PAY). Any help in how to write this formula would be greatly appreciated. Thanks in advance. Jaso -- Mes...

Replace word in sentence function...
I have the following function which replaces a word in a sentence with "....." =REPLACE(I2,SEARCH(E2,I2),LEN(E2),".....") Where the word is E2 and the sentence is I2. At the moment, if the word is "work" and the sentence is "I worked at home" I get the sentence "I .....ed at home". Any ideas how I could capture any remaining letters at the end of the word and get the result "I ..... at home"? I'd greatly appreciate your help. Thanks in advance, Chris can you upload example, would be easier to view -- Message posted from h...

find and replace hyperlink?
Hi, I want to globally find and replace the contents of a hyperlink with a new hyperlink. For example, I want this Jul.xls#'Title Page'!A1 to be replaced with Aug.xls#'Title Page'!A1 I can't seem to find a way do it. Is there a way? Thank you, Mike Take a look at David McRitchie's site: http://www.mvps.org/dmcritchie/excel/buildtoc.htm look for: Fix Hyperlinks (#FixHyperlinks) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm me wrote: > > Hi, I want to g...

Search and replace problem
Hi, I have an Excel worksheets where in several cell's a symbol was added to mark the cell's "to review". The symbol used was "(*)". I have tried now to delete all the entries with a "search and replace", but Excel interpretes the asterisk as a DOS-symbol and deletes everythng between brackets. Is there a way to avoid that or to let Excel know that I only want to delete the specific characters. TIA Melissa It's because it is a wildcard, you can replace it by preceding it with a tilde ~ which tell Excel to find an asterisk in find what p...

Formula change from cell reference to count
Formula works as is: If sheet exist matching the name in A45, then look at K39 on that sheet and return the value. I need to ammend to say: If sheet exist with matching the name in A45, then count the number of times the name is listed in Column N (or range n2:n500) on that sheet. =IF(ISERROR(CELL("address",INDIRECT("'" & A45& "'!k\$39")))," ",INDIRECT ("'" &A45& "'!k\$39")) First, I returned "" instead of " " (I don't like spaces!). =IF(ISERROR(CELL("address",INDI...

Replacing Powerpoint 97 SR-2 on M/S Office 97 Pro Edit on Win XP
I dread asking this in case someone descends on me like a ton of bricks and says the answer is in the posts already! - but i I have never used the PP 97 which came with the MS Office 97 Pro Edit I have installed on my computer which uses Win XP with Ser Pack 3. I am about to embark on using PowerPoint for the FIRST time and wonder whether I should, before anything else, acquire a more up to date version first - and one that will work problem free with my XP - which version should I go for and will it, on loading, overwrite and replace the Powpoint I already have and still operat...

Chasing formulas
I have a workbook of several daily reports. There is a summary sheet that I wish to reference the last daily report even when I add one on. Is there a "Last Sheet" function I can use? -- Curious Check this out, Don. It should be exactly what you need. http://www.officearticles.com/excel/sum_the_same_cell_in_multiple_microsoft_excel_worksheets.htm ************ Anne Troy www.OfficeArticles.com "DonB" <DonB@discussions.microsoft.com> wrote in message news:C604CF93-C6BE-4068-A5BC-FB5C9FF0E49B@microsoft.com... >I have a workbook of several daily reports. There ...

Moving cell formula 03-17-10
I have a formula that calculates averages each week. I need a formula that automatically uses the cell below when new data is entered. Hi, Average is clever enough to ignore empty cells so why not =AVERAGE(A:A) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "iamjbunni" wrote: > I have a formula that calculates averages each week. I need a formula that > automatically uses the cell below when new data is entered. "cell belo...

Refer to a defined name in a formula
Hi Thanks for reading this! I've been scouring the archives but I can't find the answer to this one. Forgive me if it's there somewhere (I'm sure it must be!) I have numerous names defined for individual cells eg. sepr, octmo etc. I am trying to create a formula that will allow me to concatenate two cells eg 'sep' and 'r' and use this to refer to the cell that the name defines. My formula looks something like =25.00*sepr and I would like it to be =25.00*(cell with 'sep')&(cell with 'r') Cheers! -- Andy. Hi Andy try =25*INDIRE...

replace a negative result with a zero?
I have my formula entered but need it to return a zero if the result is negative. Any help is greatly appreciated. -- Pam Well, what is the formula that you are using? You could try this: =3DMAX(your_formula,0) to replace your_formula. Hope this helps. Pete On Apr 19, 5:17=A0pm, Pam2277 <Pam2...@discussions.microsoft.com> wrote: > I have my formula entered but need it to return a zero if the result is > negative. =A0Any help is greatly appreciated. > -- > Pam Hi, =if(your formula<0,0,your formula) "Pam2277" wrote: > ...

Formula Transpose
In Excel2000, I have the following data in row 1 ( Cities )and formulas (starting in row 2 that reference the cities) in : A B C 1 NY LA SF 2 +A1 3 +B1 4 +C1 Is there an easy way make a formula in A2 that I can copy down.I have many rows of this type of data. Thanks Select A1:C1 and copy Select A2 Edit>Pastespecial and click Transpose -- HTH Bob Phillips "snax500" <gottschalk@worldnet.att.net> wrote in message news:1122042541.329545.193440@f14g2000cwb.googlegroups.com... > In Excel2000, I have the...

Selective Replace in Search and Replace
I need to search and replace in a large document. I don't want to replace the entire search term/expression just part of it. In this case I am searching for a paragraph return followed by any letter (not digit) and then want to change that to paragraph/tab but keep the letter. The search looks like this ^p^\$ but naturally if replace is ^p^t I lose the first letter of the text string which I don't want How can I construct either search and replace so that ^p Alexander turns into ^p ^tAlexander and not ^p ^tlexander Thanks in a...

Count If formula #2
I got help yesterday and hopefully can today. I now need a formula to count the number of "n/a" by each person. ER MD WAIT TIME TO SEE PHYSICIAN smith n/a harris 0:21 david n/a david 0:43 smith 0:25 harris 0:18 harris n/a Again, this is the same sheet and has about 2500 entries for one month. I need to find out what physicians had a wait time of "n/a" without having to sort the entire sheet by physician. Thanks. For Smith =SUMPRODUCT(--(A2:A2500="Smith"),--(B2:B2500="n/a")) replace Smith with a cell where you put the different names for better ...

I want to trace the precedents in a formula when they are on mult.
Multiple spreadsheets in a workbook. When I click on formula only shows precedent on 1st sheet I want to see on rest of sheets Paul wrote: > Multiple spreadsheets in a workbook. When I click on formula only shows > precedent on 1st sheet I want to see on rest of sheets Unfortunately it doesn't. That's something I would like to see as well. -- Registered Linux User no 240308 Just waiting for Broadband to complete the conversion!(3 weeks and counting!) gordonATgbpcomputingDOTcoDOTuk to email me remove the obvious! Depending on what version of excel (xl2002 in front of me),...

Calculated formulas in pivot tables
Hi everyone, I have a list of data that includes the following User, Login Date, Creation Date, Today's Date, Days since = (Today - Creation date) Each user has a login date and I'm counting the number of logins to see how many times they log in per days on average. I therefore use today's date against their creation date to see how many days they have been an active user and then divide their nb of logins by active days. The problem is that as the Days since column in my list is repeated several times for each login date, I therefore use Average of Days Since in the pivot tab...

Password locking one row and colum containing formula
To stop "nuisence experts" changing my spreadsheet formula (haven't we all met these people!!) I want to secure one row of cells and one column of cells whilst leaving the remaining worksheet accessable for data entry. I can lock the whole worksheet but have got bogged down trying to just secure (with Password) the cell and column containing the worksheet formula. Hi John All cells are Locked by default, which only becomes invoked when you Protect the sheet. So, select the range of cells from B2 to wherever you want, Format Cells>Protection>untick locked. Tools>...

formulas #13
What formula or particular statistic can I use to determine what the population would be ...say in 2010 in a city if I have data from 6 previous years with their populations in that same city? Look at LINEST() -- Gary''s Student "Eboneyedk" wrote: > What formula or particular statistic can I use to determine what the > population would be ...say in 2010 in a city if I have data from 6 previous > years with their populations in that same city? ...

Replace wildcards with a space
I have a text field with all values containing an asterisk. The asterisk can be located anywhere within the contents. I am trying to replace the asterisk with a space. I will then use TRIM to remove any spaces at the beginning of the text. I have been unable to get an update query to work. When my criteria is like "~[*}", Access says that 0 rows will be updated. When the criteria is [*], I am asked to enter a parameter value. Any assistance will be greatly appreciated. Thanks - bkh Barbara wrote: >I have a text field with all values containing an aste...

Transpose, but preserve, a range of formulas?
I'm using Excel 2000. I have a column (precisely, a 1-column by 12-row range) containing formulas. I want to end up with a transposed copy of this range (12 columns by 1 row) in which each cell contains exactly the same formula as the corresponding cell of the source range. For a simple example, suppose range H7:H18 contains these formulas: H7 =E7+G7 H8 =E8+G8 etc. I want to copy this range into C44:N44 so that I end up with C44 =E7+G7 C45 =E8+G8 etc. Is there an easy, direct way to do that? If not, is there an indirect way, short of brute force, and how does it work? Thanks i...

formulas in excel #3
Hi I have a column of numbers between 0 and 150. If the value is between 2 amounts say 0 and 22 then i want it to return with a value that i set. If its between 23 and 28 then return a different value. And so on up to 150. Is it possible to do this? Thanks If 7 or less try this idea starting with the higher numbers first, as shown here. If more, look in HELP index for VLOOKUP =IF(C3>120,1,IF(C3>100,2,IF(C3>80,3))) -- Don Guillett SalesAid Software donaldb@281.com "gaynie" <gaynie@discussions.microsoft.com> wrote in message news:DBED06F0-047F-4373-A531-D2E6C6...

help in moving a range of formulas
Thanks for helping. I have a range of formulas say a1 to b3. I want to move these cells with the formulas and keep the same results to say cell c1 I tried several different ways but when I do any paste I get either the wrong answers of a REF! error. I tried "copy" then "paste special" with no luck. Thanks Much Brenda Try Cut > Paste -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Brenda" <Brenda391966@yahoo.com> wrote in message news:n...

Replace more characters using replace
Hi I have number of characters in a string, that I like to replace with "nothing". I use a code like this: Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, " ", "") a = Replace(a, "&", "") a = Replace(a, "+", "") MsgBox a End Sub As I have about 15 characters, that all should be replaced with nothing, I am looking ofr another way to it. Something like Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, {" ",...

Formula #34
I need a formula that for every equivalent of \$1280 in one cell another will equal 350. i.e. a1 b1 1280 3840 a12 b12 350 1050 I hope that makes sense. A. Hi =INT(A1/1280)*350 -- Regards Frank Kabel Frankfurt, Germany "Andre" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:79be01c4845f\$f8529de0\$a401280a@phx.gbl... > I need a formula that for every equivalent of \$1280 in one > cell another will equal 350. > i.e. > > a1 b1 > 1280 3840 > > a12 b12 > 350 1050 > > I hope that makes sense....

Join two formulas
I have this formula: =SUMPRODUCT(--(YEAR('2003-2004'!\$A\$4:\$A\$10000)=2004),--(MONTH('2003-2004'!\$A \$4:\$A\$10000)=2),'2003-2004'!\$E\$4:\$E\$10000) And I only want this to be true if: {=SUM((builder="m")*(servicedby="Dave")*nc)} I don't think it is as easy as joining two together, but I tried: =IF(((builder="m")*(servicedby="Dave")),(SUMPRODUCT(--(YEAR('2003-2004'!\$A\$4 :\$A\$10000)=2004,--(MONTH('2003-2004'!\$A\$4:\$A\$10000)=2),('2003-2004'!\$E\$4:\$E\$ 10000))),1)) I also tried: =SUMPRODUCT(--(YEAR('2003-2...

Replacing old Windows XP and Outlook Express with new Windows 7
How do I export the contents and settings out of Outlook Express and import the accounts, settings and emails into Windows Live Mail on another machine? For messages: Copy the *ENTIRE* OE message store folder to flash or CD. (Folders.dbx must be included). Place this on the Desktop or other location on the machine using WLMail. Open WLMail and: File | Import | Messages | Microsoft Outlook Express 6 and point to where you saved it. For Addresses: Open the Address Book in OE and File | Export | Address Book (wab) and save it to the Desktop. Copy to flash or CD. Place this on the...