HOW DO I COPY FORMULA WITHOUT CHANGING CELL REFERENCE

My cell c1 contains the formula b1-a1.when i copy this formula to cells d1 
and e1 the cells d1 and e1 have the following formula :
d1=c1-b1
e1=d1-c1

but i want the following
d1 should be b2-a2 and
e2 should be b3-a3

how do i do this?
0
anantth (1)
2/6/2005 8:39:01 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
348 Views

Similar Articles

[PageSpeed] 41

One way...

In C1 enter & copy across:

=INDEX($A:$A,COLUMN()-COLUMN($C$1)+1)-INDEX($B:$B,COLUMN()-COLUMN($C$1)+1)

anantth wrote:
> My cell c1 contains the formula b1-a1.when i copy this formula to cells d1 
> and e1 the cells d1 and e1 have the following formula :
> d1=c1-b1
> e1=d1-c1
> 
> but i want the following
> d1 should be b2-a2 and
> e2 should be b3-a3
> 
> how do i do this?
0
akyurek (248)
2/6/2005 9:14:16 AM
Copy C1 and paste it to C2:C100 or however many you need.
Select C2:C100 and do edit / Replace, replacing = with [
Select C2:C100 and copy
Select D1 and do Edit / Paste Special / Tranpose
Select D1 across to the last entry and do Edit / Replace, replacing [ with =
Delete what was in C2:C100

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------------------------------------------------------------------------
                  It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"anantth" <anantth@discussions.microsoft.com> wrote in message
news:5D3D2C9A-86A0-4BE3-9C96-148594429575@microsoft.com...
> My cell c1 contains the formula b1-a1.when i copy this formula to cells d1
> and e1 the cells d1 and e1 have the following formula :
> d1=c1-b1
> e1=d1-c1
>
> but i want the following
> d1 should be b2-a2 and
> e2 should be b3-a3
>
> how do i do this?


0
ken.wright (2489)
2/6/2005 11:44:53 AM
Another way ..

Put in C1:

=OFFSET($B$1,COLUMNS($A$1:A1)-1,)-OFFSET($B$1,COLUMNS($A$1:A1)-1,-1)

Copy C1 across to E1

C1 to E1 will return :

B1-A1
B2-A2
B3-A3
etc

--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
anantth <anantth@discussions.microsoft.com> wrote in message
news:5D3D2C9A-86A0-4BE3-9C96-148594429575@microsoft.com...
> My cell c1 contains the formula b1-a1.when i copy this formula to cells d1
> and e1 the cells d1 and e1 have the following formula :
> d1=c1-b1
> e1=d1-c1
>
> but i want the following
> d1 should be b2-a2 and
> e2 should be b3-a3
>
> how do i do this?


0
demechanik (4694)
2/6/2005 12:12:27 PM
Think Aladin meant in C1, copied across:

=INDEX($B:$B,COLUMN()-COLUMN($C$1)+1)-INDEX($A:$A,COLUMN()-COLUMN($C$1)+1)

(the other way around <g>)
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
Aladin Akyurek <akyurek@xs4all.nl> wrote in message
news:4205DFE8.9050703@xs4all.nl...
> One way...
>
> In C1 enter & copy across:
>
> =INDEX($A:$A,COLUMN()-COLUMN($C$1)+1)-INDEX($B:$B,COLUMN()-COLUMN($C$1)+1)
>
> anantth wrote:
> > My cell c1 contains the formula b1-a1.when i copy this formula to cells
d1
> > and e1 the cells d1 and e1 have the following formula :
> > d1=c1-b1
> > e1=d1-c1
> >
> > but i want the following
> > d1 should be b2-a2 and
> > e2 should be b3-a3
> >
> > how do i do this?


0
demechanik (4694)
2/6/2005 12:25:53 PM
Reply:

Similar Artilces:

reformat text in a cell
I have 2000+ cells that look like this: UK00001 Geddy Lee 2112 Bytor Ln GLENVIEW, IL 60025-1522 Is there a simple way to add a space after each line? right now when i paste it into a word doc, it shows like this : UK00001 Geddy Lee2112 Bytor LnGLENVIEW, IL 60025-1522 I would like to REFORMAT every cell to look like this with spaces in between to make it readable: UK00001 Geddy Lee 2112 Bytor Ln GLENVIEW, IL 60025-1522 any suggestions? ...

How can I translate a comment to text in a cell?
A cell has existing text in it and it has a comment attached to it. I'd like to append the comment to the end to the existing text in the cell. Has anyone done this already? Is it possible without going into each and every comment? Thanks, Chris ASAP utilities has a UDF formula called "ASAPGetComment()". If you install this utility (which I highly and impartially recomend), you could use this formula to concatenate the two together. http://www.asap-utilities.com "DFIChris" wrote: > A cell has existing text in it and it has a comment attached to it. I...

how do I change picture resolution of all pictures at one time?
I have a large catalogue with over 150 pictures. I have the high res pictures in the file for commercial printing, however would like an e-mailable catalogue and in order to do this I need to change the resolution of all the pictures. Is there a way of doing this all at once? Thanks, Stephen print the cat. to a pdf and email the pdf to your clients. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "Stephen" <Stephen @discussions.microsoft.com> wrote in message news:344EE8B6-8748-4271-B8C6-C77663F7AB80@microsoft.com... > I have a large catalogue...

How to incorporate 2 sets of changing named ranges
Hi All I'm a novice that needs help please..... sorry - I've tried but I'm stuck.... (one workbook, heaps of worksheets) I've created a macro that runs a set of actions for multiple named ranges by calling each (see code below) It finds / copies / pastes data from different worksheets & named ranges into an overview. The macro adds a 1 or 2 or 3 to "collectionMT" and repeats the actions against each named ranges: CollectMT1, CollectMT2, CollectMT3, CollectMT4 etc through to CollectMT9 I don't know how to do the same (in the same macro) for t...

Formulas act like text
When I try to enter a formula, it acts as if it is text. If I try co copy from the formula line and paste to the formula line of the destination cell, the same thing happens. When I click to the right of the formula in the formula line, it says VLOOKUP in the Functions space to the far left, even though there is no vlookup function being invoked in the entire sheet? I don't even know how to describe the problem well. Any suggestions would be appreciated. Bill Any cell in which you enter a formula? Could be you are in View Formula mode. Hit CTRL + '(backquote above Tab key)...

Saving without formulas
Is it possible to save a spreedsheet with only the the cell values and not the associated formulas. Basically, what I want to do is a giant Copy PasteSpecial Values across an entire workbook Thanks Bob "Bob Ewers" <anonymous@discussions.microsoft.com> wrote in message news:274FD675-AB2F-4295-A201-71B4AE2C6C2A@microsoft.com... > Is it possible to save a spreedsheet with only the the cell values and not the associated formulas. Basically, what I want to do is a giant Copy PasteSpecial Values across an entire workbook. > > Thanks. > > Bob Group the worksheets ...

formula help:adding without regard to +/-
How do I tell excel to add a column of numbers without regard to +/- sign. I want to know the gross total of all adjustments, not the net. Here's the scenario. A house sells for $200,000, we make adjustments for differences, say -$1,000 here, +3,000 there, I want excel to tell me that I've made $4000 in adjustments - not +2000. Thanks for your help. Vivian =SUM(ABS(E1:E10)) entered with ctrl + shift & enter or =SUMPRODUCT(ABS(E1:E10)) entered normally -- Regards, Peo Sjoblom "Viviank" <Viviank@discussions.microsoft.com> wrote in message news:...

change column to rows with a match
I need a worksheet formula that can do this : data in columns: result: A B U V W X Y Z 1 bill 22 1 bill 22 7 4 7 22 2 bill 7 2 joe 5 5 3 bill 4 3 ian 8 10 8 6 4 bill 7 4 ann 3 8 10 5 bill 22 6 joe 5 7 joe 5 8 ian 8 9 ian 10 10 ian 8 11 ian 6 12 ann 3 1...

copy data from master sheet
my sheet1 is master sheet where data related to all continent is entered. e.g. A B C D ....... 1 America US Big 1 2 Europe Britain Big 3 3 America Canadan Medium 7 i want my sheet2 to get all information if column A contains america. (i.e. row 1 & row 3 data) ...i.e. sheet 2 is america specific sheet.. similarly sheet3 to get all data if Column A contains Europe (i.e. row 2 data). It should update automatically whenever i enter data in master sheet. i dont want to u...

Please help with this formula
I have a spreadsheet; 25 participants for 31 days each participant must log a catagory (eg PC) the spread sheet count the number of PCs for the 31 days (eg. formula (=COUNTIF(E10:AI34,"PC")). Question how can I calculate how many participates have logged a catagory PC within the 31 days. Hi C, One way, Put =COUNTIF(E10:AI10,"PC") in cell AJ10 And drag down to AJ34 Then in another cell of your choice put =COUNTIF(AJ10:AJ34,">0") HTH Martin "C Sealy" <C Sealy@discussions.microsoft.com> wrote in message news:13350DAB-8BEC-4731-B235-B4B...

Excel formula fill. Help needed...
Group, I are trying to fill a number of rows from a formula that is based on previously calculated data. For example, column D has values based on Column B where D3 is the sum of D2+B3. I auto filled Column D the desired number of rows and the rows filled in properly. Now, Column E is based on Column D minus a constant of 234 with the same pattern as Column D where E3 is the difference of D3-I4 (the cell with the value 234). Now, how would I fill in Column E the desired number of rows (85). Thanks, Jim =D3-$I$4 "Jim" <Jim_mace@Hotmail.com> wrote in message new...

DNS and ISP Change
Hi again all. We currently have a 256k leased line from NTL and are about to move premises, where a BT 2MB pipe is waiting. NTL will still host the e-mail domain, so my MX record will remain the same, right? So am I right to think I just need them to create a new A record to reflect my new BT IP address and configure Exchange to use the BT DNS servers? What about Name Servers, do I need to ask NTL to update Name Servers to use BT ones....? Sorry if this doesn't make sense. I've kinda been droppped in at the deep end. Thanks, in advance. You're right in your first assumption if N...

VB reference to table field
I've got this code, but I need to figure out the syntax for the !COMMENT part. I want it to refer to the master table's COMMENT field. This code, however is in the main form. I've tried [Tables]!Table1.Comment among others, but it gives an error. Any help? Thanks!!! With Me.RecordsetClone .AddNew !Name = Me.Name !DOB = Me.DOB !COMMENT = .Update "Mark1" <Mark1@discussions.microsoft.com> wrote > I've got this code, but I need to figure out the syntax for the !COMMENT ...

MIN ingnoring zero sum in an range of cells
I have a spread sheet where I need to use =min(a1:a10,b1:b10) but ignoring any cells with a zero in the range. Please hel. I have been searching the groups for hours. Tks in advance On Dec 22, 3:18=A0pm, Gulicio <michael.gul...@snet.net> wrote: > I have a spread sheet where I need to use =3Dmin(a1:a10,b1:b10) but > ignoring any cells with a zero in the range. Please hel. I have been > searching the groups for hours. Tks in advance Finally found answer that worked for thoose still looking here it is: try the following array formula (entered with CTRL+SHIFT+ENTER): ...

Unable to save Changes to my report
I am having problems. this is my second question today. One of my reports had an invalid (non-existant) field for a sort. I deleted it and the error message goes away. I am prompted to save the changes. I say yes, but the report will not close and the changes have not been saved! the only way to close the report is to say no! HELP!! There's a good chance that the report or the database is corrupt. Try deleting the report the rebuilding it. If you have a recent backup, you could import the report. Tony Toews has an excellent web page on database corruption. http://www.granite.a...

Copying a form to the same sheet using macro
Hi, Hope I can clearly demonstrate the problem=85..I am not so good in these=85. In =93Jan =9110=94 sheet I have created a form to capture the sales for wee= k 1. If I want to copy Week 1 sale and make another form in the same sheet using macro=85..how do I do that. When I copy week 1 form to week 2=85..next I want to copy week 2 to week 3 by clicking a command button and I would like to do this using the same code. Next problem is, I want the summary to add up in a =93Summary=94 sheet. Is it possible to add up totals as I include the new forms (i.e. week 2, week 3)? If someone...

Changing mode to IMO; Unable to Open Outlook
My computer got a virus. After saving my data, I cleaned the C: drive and reinstalled windows and Office2000 Duuring installation of Office inadvertantly hit corporate mode rather than IMO mode. Now I cannot get back to IMO and recover my personal folders with all my data. In fact I can't seem to get anywhere. I tried: a) Uninstalling Office and reinstalling. Nope. b) Uninstalling Outlook and reinstalling. Nope. Currently, when trying to run Outlook, I get, "Unable to open your email folders. Outlook could not start because a location to send and receive ema...

Formula on Actual Vs Plan
Hi Friends! Could some help me on this formula? I need to complete a task say from 1-Jun-05 to 30-Jun-05 i.e 30 days, but however on a given day when I analyse the Project schedule (say today), I find out that on the 9th day i.e 9-Jun-05 on actual progress I have completed only 4% of the Job, Hence what should be my forecasted days to complete the job a 100% in actual, now putting it in a mathematical sense: 100% = 30days (Plan) 4% = 9days (Actual & when the plan is 30%) Hence 96% = ? (How may days in Forecast) I want the formula to be dynam...

Count Formula #4
I have a spreadsheet with three tabs. They all have people requesting time off...first sheet is for people requesting more than a week off - 2nd for 1 week at a time - 3rd for people just wanting 1 day off. The sheets have their name then 2 columns one for days off starting and the other for days off finishing (With the exception of people just wanting one day off) I would like to have another sheet counting the number of people off on a daily basis. It is easy enough using "counif" for the people asking for 1 day off...but I can't seem to easily break out the weekly...

sending v cards, changing address settings
Hi i use outlook 2003, When I send a v card, the address fields change to businness, which is normally blank. I have set the home address as the address to use as mail address, but still this happens. I tried forwarding the contacts, not as v cards, which does not have the same problem, but found this duplicates the contact in my outlook. any ideas thanks tim ...

how to copy formula from one workbook to another
Hi! I have a blank workbook containing many formulas and many worksheets. I use this to create a new workbook everymonth and enter data. After a while, I found a way to improve in a subset of formulas. How can I go back and reapply these formula to the entire workbook (including all worksheets, some of them are identical but others are different) without doing lot of manual work? thank you, prashant -- How about the paste special? It didn't work or not likely what you wer looking for -- hidek ----------------------------------------------------------------------- hideki's P...

graph changed from the last time I worked with it
I have a simple line graph and when I went back to it the dates in the date column had changed to sequential numbers eg. 39088, 39089, . . . and Date now appears in the legend. I tried to get back to hoe I set it up with Wizard but only am able to get wizard to take me to a new setup. Hi, I'm not clear on the question, but here are a few ideas: 1. You can't get back into the charting wizard unless the chart is selected. 2. If you are asking about the number 39088 - these just need to be formatted as dates, Excel stores dates as integers and times as decimals, so together y...

Using a Message Box to display �Random Quotation� stored in cells
Hi Can any one help me with this one ? What I�m trying to do is to click on a button which runs a Macro whic in turn selects a random quotation i.e. �to be or not to be�.� whic is already stored in a cell. I have about twenty quotes but I need to added to them all the time s the list would keep on growing Any help would be greatly appreciated Many thanks Ro -- Message posted from http://www.ExcelForum.com Hi Rob if your quotes are in column A of the active sheet try the following code (assign this to a button on the active sheet): Private Sub CommandButton1_Click() Dim wks As Wor...

Error in sum function when using merged cells
Help! I recently discovered an error in my shipping worksheet. sometimes need to merge certain cells when I am combining items fro different purchase orders into the same shipment. However, when totaled the column (SUM) containing the merged cells, it delivers wrong answer. Specifically column F and column H that contain the merged cells don' add up correctly. Column F is +1 more than it should be. Column H i +125 more than it should be. I have been using this feature for about 6 months, and this is th first time it did this. Any ideas why the error +----------------------...

Question about autocoloring cells?
Hi all, I have a question about autocoloring cells, rows, columns, etc. This is not about conditional formatting - it's about, for example, coloring every other row automatically per some function or style setup. The example I have is a row-based database of recruiters. As my contacts list grows and shrinks, I have been having to manually recolor every other row light blue for visibility purposes. Not that much of a hassle but (a) I'd prefer not to do it and (b) if Excel has a way to do it for me, so much the better, I learn a new feature of Excel. Is there a way that Excel can a...