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
292 Views

Similar Articles

[PageSpeed] 1

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:

NumberFormat not getting changing onFly changing the regional sett
Hi Excel experts , Sorry for posting a new question over here. I am having a hard time with one of a Excel related issue. Requirement: 1) User pulls some data ( includes numeric,Date fields ) to excel when he/she is with German OS settings. Date show in following format: dd.mm.yyyy ( eg: 23.09.2010) 2) Now on the fly he/she changes the OS regional settings to En-US 3) Here our code again reformats those columns with current regional settings and polish the data according to its regional settings. 4) In our case all numeric values are getiing changed correctly to the ...

Change Fileas name to first name last name
Hi Where can I change the settings so that the contacts filed as First Name last name instead of Last name,First name Thank Jason OL2002: How to Change the File As Field for All Contacts ID: 291144 -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply to this newsgroup. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm "Jason" <anonymous@discussions.microsoft.com> wrote in message news:7F277C...

Copying junk senders list
Hi, In Outlook 2000 and 98, how do you copy a junk senders lists to put on another client account? Thanks ...

Writing programs for the manipulation of data cells?
I'm new to Excel and I think I can do this with macros. In that case, where can I find a list of Excel functions? On 18 Mar, 10:38, "Scott H" <nospam> wrote: > I'm new to Excel and I think I can do this with macros. In that case, where > can I find a list of Excel functions? Have you tried the help? I would also highly recommend Chip Pearson's site: http://www.cpearson.com/excel/MainPage.aspx The great thing about Excel is there are so many high quality advice sites that are easily found. ...

optical trackball
I like to move my MS optical trackball with my fingertips. This means having it "upside down" (cord towards me). On my previous computer, XP provided settings > control panel > mouse > activities which included an "orintation" setting. My newer one doesn't have "activities". Is this something to do with "Intellipoint"? Hugh Momoka wrote: > I like to move my MS optical trackball with my fingertips. This means having > it "upside down" (cord towards me). On my previous computer, XP provided > settings > con...

Change the mail path
My IMAP server is UNIX-based, my e-mail is stored in its own directory in user home directory-for example: ~username/Mail. i have windows -xp in compaq system. which is part of lan. i want to change the path of default mail(i.e where d mails r saved.) please help me out. The instructions gvn in help is not able to help me out. Please guide Thanx in advance../// Raghav wrote: > My IMAP server is UNIX-based, my e-mail is stored in its own directory in > user home directory-for example: ~username/Mail. i have windows -xp in > compaq system. which is part of lan. i want to...

How to change font in Status row and Tooltip
Hi! Is it possible to change the font in the status field and on tooltips that gets displayed. I'd like to change to a font that can display Cyrilic characters. Thanks in advance! /Henke ...

Determining Changes between an Old and Updated Database
Each week, I recieve an updated database and would like to be able tocompare the new database to the old one to determine exactly whatchanged from the new to the old. Any ideas? Set up linked tables so you can access data in one database from theother; and then use queries (especially but not only Find Unmatchedqueries) to examine the data. On 5 Mar 2007 09:49:27 -0800, quinnculver@gmail.com wrote:>Each week, I recieve an updated database and would like to be able to>compare the new database to the old one to determine exactly what>changed from the new to the old. Any ideas?--John Nur...

Understanding complex IF Formula within IF formula
I do not know what this formula is doing can you help? =IF(LEFT(QBCash!$B2,5)="Total","",IF(ISBLANK(QBCash!$B2),$A1,QBCash!$B2)) Thanks for your help. DP Hi, The formula is looking at a worksheet called QBCash Breaking the formula down =IF(LEFT(QBCash!$B2,5)="Total","", 1. It looks at the contents of B2 and if the word 'Total' are the first 5 letters of the cell the formula return a null string IF(ISBLANK(QBCash!$B2),$A1, 2. If that isn't TRUE it then checks if B2 is blank and if it is it returns the value of...

Users locked out after changing password
We are issues with users changing their passwords in Dynamics GP, existing the application, and upon logging in again with their new password, getting immediately locked out of the system. They then have to be unlocked at the sql level, but upon changing the password, the same thing happens. We have Enforce Password Policy on, and we are on SP3 of Dynamics GP. Does anybody have any insight to resolving this issue? ------=_NextPart_0001_6BB9EA6B Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Randall It is possible that the user is getting locked out as they have changed so...

adding pictures/logos to crm or changing colors etc
does any one know if it's possible to incorporate pictures or logos to form or the CRM UI as a whole, if so how would i go about doing this? also is it possible to change other features of its appearance such as colors...? TIA Emily, The place to start might be by looking at the cascading style sheet template that is documented on the SDK - this is really meant to provide a base for building other web sites in the style of MS CRM. >-----Original Message----- >does any one know if it's possible to incorporate pictures or logos to form >or the CRM UI as a whole, if so...

How can I change worksheet direction from right to left
I want to change the direction of the work sheet in Excel 2007 from left to right and vice versa If you mean you want to change the direction of the cursor on Enter, then click on the round logo top left, select Excel options, select Advanced, and change the direction to Left, Right, Up or Down as you desire. -- HTH Kassie Replace xxx with hotmail "Haitham" wrote: > I want to change the direction of the work sheet in Excel 2007 from left to > right and vice versa I believe the only way is to go through Microsoft Office Tools and make the setting ...

how many characters i can put into one cell in Excel
If i typed a whole paragraph into one cell of Excel, only a part of para show up in that cell. I tried wrap text, increase the height of the column. Nothing happen. Any one have idea about it or there are some limit to view in Excel in one cell Hi Search for 'specifications' in Help: It says '32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.' -- Andy. "Gary" <Gary@discussions.microsoft.com> wrote in message news:747E6CFE-88D9-4F4C-950E-56F0885C3096@microsoft.com... > If i typed a whole paragraph into one cell o...

Cant change calendar background color
Hello. I am on a Windows xp pc running Outlook 2003 all patched up in a windows/exchange client/server environment. I have ownership rights to another exchange mailbox. I just need to know if there is a way that I can change the background color of that secondary calendar so that it is different from my own calendar. The help menu in Outlook says > on the Tools menu, click Options, and then click Calendar Options. Under Calendar options, in the Background color list, click the color that you want. Easy enough. However, even if I put the focus on the secondary calendar and change the...

Reflect cell contents into another worksheet #3
I have forgotten how to reflect data that is present in one worksheet cell into a specific cell of a second worksheet. IOW, repeat cell contents in a second worksheet. I simply do not make use of MS Excel97 often enough to remember the procedures ! Please help. Regards, ~ Vince ~ You can just type = and then click the cell if you want to be duplicate. "Vince" wrote: > > I have forgotten how to reflect data that is present in one worksheet > cell into a specific cell of a second worksheet. IOW, repeat cell > contents in a second worksheet. > > I simply do...

building an external link based on a cell value
I would like to build an external link in a spreadsheet that automatically changes based on a cell value. For example, I have the following link: =SUMIF('[Location Income Statements 2004.xls]Mar04'! $14:$14,P5,'[Location Income Statements 2004.xls]Mar04'! $18:$18) Instead of Mar04 in the formula above, I would like to use a cell's contents.... cell A8 for example. Is this possible? Thanks, Jason Jason You can use the INDIRECT function. =SUMIF(INDIRECT("'[Location Income Statements 2004.xls]" & A8 & "'!$14:$14"),P5... Indirect ...

Formula?
I'm trying to add a formula in a cell which will leave the cell blank if column A is blank. I use the following: ActiveCell.FormulaR1C1 = "=if(+RC[-10]>0,2.5,"")" but I get a run-time error (1004). I know the problem is the quotes in the third argument. I tried replacing the quotes with zero and it enters zero correctly with no error. But I need the cell to be blank, not zero, if column A is blank. Hi Try four quotes instead of two: "=if(+RC[-10]>0,2.5,"""")" -- HTH. Best wishes Harald Followup to newsgroup only please ...

new emails don't show without clicking send-receive
new emails don't show without clicking send-receive button--and now I've lost the send/receive button. What do I do? thanks ...

Getting any formula to stop at the next blank space
I frequently write VLOOKUP and SUMIF formulas for my spreadsheets an have found myself typing the same thing over and over. How can I ad in a command that makes each formula stop at the next blank. I als have to make the formula references "absolutes". What I'm wanting t do is copy and paste the same formula down without sacrifices th absolute references -- ajpower ----------------------------------------------------------------------- ajpowers's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=940 View this thread: http://www.excelforum.com/showth...

Font size changes in Excel 2003 charts when re-opened
For certain charts that I create within Excel 2003 (usually bar charts), I use a font size of 8 (eg for data labels, axis labels, and scale). If I close the file and then re-open it, all these fonts will have become size one (title and legend are not effected). Oddly, if I have created a bar chart and made several copies of it within a worksheet, it will only be the original and not the copies that are affected by this. If anyone can shed any light on why this may be happening and how it could be fixed, I would really appreciate it. Lucie ...

Using VBA to change Excel layouts
Our company has designed 4 different spreadsheet designs. The same data gets stored in any of the 4 spreadsheet layouts....the only difference is that some spreadsheets use different colors, have different fonts and cell sizes and etc... I thought it would be nice to create just ONE spreadsheet that contains a combo-box or list-box or drop-down box which allows the user to select 1 of the 4 layout choices, then I want VBA to programattically setup the spreadsheet layout according to the users choice. Does that make sense? Does anybody know any good ways to implement something lik...

Linking a cell to a chart title
How do I link a chart title to a cell reference in another worksheet? I have over 200 graphs in one spreadsheet and need a quick way to update the titles when mgmt changes for each sales team. ...

Office updates changing Outlook settings?
Hello, Has anyone experienced some of their Outlook settings changing when doing Office updates? Thanks, Jerry Jerry, Which settings were changed, can you be more specific please. I have not seen where doing a general Office Updates changes the standard settings. -- Regards, Francine Otterson President, San Diego Outlook User Group MVP - Microsoft Outlook "Jerry" <jerrypav456@hotmail.com> wrote in message news:upt1139gpu6vjvgelcc72gstnq2rjjfj1i@4ax.com... > Hello, > > Has anyone experienced some of their Outlook settings changing when > doing Office upd...

Naming a cell
This was a question given on a Excel test I was given. Rita, To name a cell or range of cells, just select the cell(s) and enter the name in the Name Box, located to the left of the formula bar. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Rita" <anonymous@discussions.microsoft.com> wrote in message news:03cb01c3c630$17c56c50$a001280a@phx.gbl... > This was a question given on a Excel test I was given. "Rita" <anonymous@discussions.microsoft.com> wrote in message news:03cb01c3c630$17c56c50$a00128...

the font changes colour on emails I send
I am sending an email and the font looks correct. When the recipient receives it some of the text is bright green. It doesn't happen on every email but when it does, it is only on part of the text. ...