absolute cell references

I've got monthly expense numbers in one workbook I need to 
compare to monthly expense numbers in another workbook.  I 
insert a new column to calculate the differences in one of 
the worksheets.  Then I simply click to create the formula 
that subtracts the expenses in the current workbook from 
the expenses in the other workbook.  Here's the problem.  
I'd like to set up one cell with the difference and then 
just copy it to fill in the rest of the months, right?  
Well, for some reason, when I create the first cell, it 
refers to the other workbook using an absolute cell 
reference.  Does anyone know how to turn that off without 
always having to use F4?  I would prefer the default 
action to be a non-absolute cell reference.

Thanks!
Chazz
0
8/14/2003 9:17:36 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
551 Views

Similar Articles

[PageSpeed] 42

This may not be a good fit for you, but if you copy (or move) the other
worksheet into the same workbook, then the default goes to relative addressing. 
(Worksheets in different workbooks default to absolute addressing--like you
noticed.)

A lot of times, I'll copy that sheet into my workbook, do my formulas, convert
to values and delete that sheet.  (Without too much testing (ok, absolutely
none), I get the feeling that calculation works faster when the data is in one
workbook.  But that might just be my imagination.)

Chazz wrote:
> 
> I've got monthly expense numbers in one workbook I need to
> compare to monthly expense numbers in another workbook.  I
> insert a new column to calculate the differences in one of
> the worksheets.  Then I simply click to create the formula
> that subtracts the expenses in the current workbook from
> the expenses in the other workbook.  Here's the problem.
> I'd like to set up one cell with the difference and then
> just copy it to fill in the rest of the months, right?
> Well, for some reason, when I create the first cell, it
> refers to the other workbook using an absolute cell
> reference.  Does anyone know how to turn that off without
> always having to use F4?  I would prefer the default
> action to be a non-absolute cell reference.
> 
> Thanks!
> Chazz

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
8/15/2003 1:13:05 AM
Reply:

Similar Artilces:

Pasting into an active cell
I have some VBA code (see below) where I am copying a fixed selection from one workbook (which the user has selected), into the next empty row in a different workbook (where the macro is located). Sheets("National").Select Range("A6:X21").Select Selection.Copy Windows("Diagnostics 2010-2011.xls").Activate Sheets("C-National").Select Selection.paste However, I get a run time error (438) on the paste command when I try to run it. Any suggestions, as I am new to VBA Try the below which will copy the range from the Activeworkbook Sh...

Can we tell which cell an image is on?
The user is clicking on a button that says "HIDE" and when the user hits it.. it hides the row where the button exists. IS THERE A WAY I CAN DO THIS? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Hi kenji4861, Are you talking about an image or a CommandButton? If it's a CommandButton, you can use code like this: Private Sub CommandButton1_Click() With CommandButton1.TopLeftCell.EntireRow .Hidden = Not .Hidden End With...

Change Time in a Cell from [hh]:mm to decimal value ie 16:45
Here lies the problem. I'm cheap, I want to be paid for every minute that I work, I do the time cards for work and I'm using Excel to perform the functions. I have no problem with adding and subtracting the times in single cells or totalling all of the time. If I come up with a person having 47 hours and 13 minutes how do I get Excel to convert 47:13 to 47.22 so that I can then multiply the 40 by hourly rate and the 7.22 by 1.5 the hourly rate ? -- Thank You, God Speed, and Semper Fidelis Hi! This all depends on how you have things setup. To convert 47:13 to 47.22 just mu...

Automatic Changes to Linked Cells
I eed to find a way to link to a cell and have the link follow that cell should it chanage due to editing of the spreadsheet. For example in Spreadsheet 1 I linked cell A1 to cell B1 in spreadsheet 2. Then I made changes in spreadsheet 2 and the contents of cell B1 are now located in cell B5. How do I get the link in spreadsheet 1 cell A1 to automatically change to be linked to cell B5 in spreadsheet 2? The version of excel that I have automatically puts in the "$" before the cell letter and number. I have done a test by taking out the "$" to see if it would...

Cell value as chart scale maximum
I have a chart on a worksheet and want to make the Y scale maximum value equal the value of cell Y3 (this is a merged cell range Y3:Y5), rounded up to the next “5” increment. For example, 101% in merged cell Y5 would be 105% in the chart Y scale maximum. How would I do this? Hi Phil - Use the approach I describe here: http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html but link to CEIL(Y3,0.05) instead of Y3. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Phil Hageman wrote: > ...

Turning value of formula into actual cell content?
Hi. I've a sheet with a few thousand rows where in column B I need to add a prefix to every value. What I did was in column M fill the cells with the prefix, and made column N a copy of column B. Then in Column B I made the function: =M2&""&N2 and copied it all the way down (with incrementing numerals). That works great. But, I need to be able to copy-n-paste the new prefixed value of some of the cells in column B into another spreadsheet. But when I try, what I'm copying is the formula, not the resulting value. Is there a way to copy the values generated by the formu...

locking cells 04-27-10
excel version 2003 i have 4 cells that once one is select i dont want the user to be able to type in the others is there anyway to control this Cells are locked by default but has no bearing until you protect the Worksheet. IF only 4 cells, Ctrl+A > Format cell - Protection - unchecked Locked. Now select your 4 cells and Lock them, then protect the Worksheet. -- Regards Dave Hawley www.ozgrid.com "rwbaker" <rwbaker@discussions.microsoft.com> wrote in message news:EDC477FA-BC54-41B0-B0E5-72C33A717EEE@microsoft.com... > excel version 2003 > i have...

Green tick in upper left of cell
In Excel XP I have noticed a green tick mark in the upper left corner of a cell. Can anybody tell me what this tick mark is about? Thank you in advance. Hi Mark Tools>Options>Error Checking Tab http://support.microsoft.com/default.aspx?scid=kb;en-us;291361&Product=xl2002 -- Regards Ron de Bruin http://www.rondebruin.nl "Mark Watlock" <anonymous@discussions.microsoft.com> wrote in message news:93d901c43377$4d530df0$a501280a@phx.gbl... > In Excel XP I have noticed a green tick mark in the upper > left corner of a cell. Can anybody tell me what this tick...

Cell Editing
How to you set the program to allow cell editing followed directly by using a cursor key instead of ENTER (like Lotus....) Hit F2 to enter edit mode(see Edit on Status bar). Arrows will move within the cell. Hit F2 again to enter "Enter" mode(see Enter on Status bar). Arrows will take you out of the cell. Gord Dibben Excel MVP On Fri, 11 Feb 2005 08:57:05 -0800, "remco2mill" <remco2mill@discussions.microsoft.com> wrote: >How to you set the program to allow cell editing followed directly by using a >cursor key instead of ENTER (like Lotus....) Look at...

Cell Formating/Sorting
I have a worksheet which has 2 columns. Fist column contains time/date data in the format dd/mm/yy hh:mm. The second column contains data relating to the first column. The data is sorted by date. I wish to sort the data by time. I have tried to copy the first column into a new column, changed the format of the cells to date for the one and time for the second. Although the data in the cell appears to show either date or time, clicking on a cell still shows the data in the original format, so it won't sort by time. Any help appreciated. Regards Mike Mike Dates are whole numbers ...

averaging from one selected cell to another #2
I have a table with x and y values. I have another z column. In the z column i want to be able to average between the x value along that row location to the cell that has a value below a certain number say 4y. ...

Finding next blank cell
Hi all, I use this code to find the last non blank cell. ActiveSheet.Range("G65336").End(xlUp).Select How would I modify this to select the next blank cell after that cell? Rob Rob ActiveSheet.Range("G65336").End(xlUp).Offset(1,0).Select Regards Trevor "rob nobel" <robnobel@dodo.com.au> wrote in message news:#$NfmCwqDHA.1656@tk2msftngp13.phx.gbl... > Hi all, > I use this code to find the last non blank cell. > ActiveSheet.Range("G65336").End(xlUp).Select > How would I modify this to select the next blank cell after that cell? &g...

Excel mistakes cell formats
Hi, With certain forumlas, putting them in a cell formatted with a =ABGeneral=BB format (and all the cells around this cell have a General format too), this cell trasfoms its format to Text. The result has a left align, and if I edit the forumla and reenter without modifies, in the cell appair the text of the forumla instead the value. I don't know if it's a localization bug (I have Excel in Italian), but the follower formula: =3DCOUNTIF(Sheet1!$A$13:$A$147,Sheet2!a13)+E4-1 has the bug, while the follower: =3D(COUNTIF(Sheet1!$A$13:$A$147,Sheet2!a13)+E4)-1 hasn't Saved from a prev...

Insert text in a cell with a macro
I have a series of numbers (SSN) that I need to turn into text, so I want a macro that will put an apostrophe and a zero at the beginning of the numbers. I know that there are other ways to change a number to text (formatting, find/replace to add the apostrophe) but for what we are doing, manually editing to add the apostrophe and zero is the only thing that works. SO.. to my question. When I make the macro to do this, of course it simply adds the apostrophe and zero and repeats the number from the first cell over and over again. How can I make it select each cell's new data, mo...

adding cells which include numbers and text
Hi, I am hugely ignorant regarding excel, no training, and apparrently no brain... What I need to do is allocate a number to a placing i.e. 1st = 100, 2nd = 99, 3rd = 98 etc. If A5 (for example) has 1st in it, I put in A6: =101-A5, but it will not add it because of the text. How do I get the formula to ignore the text in A5??? Shelley ;) Hi! One way: =101-SUBSTITUTE(A5,RIGHT(A5,2),"")*1 Biff "Shelley" <shelleymcadam@hotmail.com> wrote in message news:1130215231.476700.153960@g47g2000cwa.googlegroups.com... > Hi, I am hugely ignorant regarding excel, no trai...

Change cell color based on variable condition
I have a large table with 5 data columns and a sum column: A B C D E F 1 2 2 2 2 2 10 2 2 8 2 2 2 16 3 1 1 6 1 1 10 4 1 2 1 2 8 14 5 1 2 3 4 5 15 The actual data is much larger (1E10). I would like to change the color of each cell that meets some criteria, such as it is >= 50% of the sum (F). In the table above, that is true for B2, C3, & E4. So I;d like to turn those cells yellow and leave the other unchanged. I would also like to be able to change the formula in one place and have all cells recalculate. Failing that, I'd like to be able...

Pointer of Reference ?
Hi guys, I still don't understand the difference between a pointer and a reference. Maybe I can learn with this example: void MyFunction(CDC* pDC,CBitmap* pBitmap) { // do some stuff } // call that function CDC myDC; CBitmap myBitmap; .... MyFunction(&myDC,&myBitmap); Or should I do it like this: void MyFunction(CDC& dc,CBitmap& bitmap) { // do some stuff } // call it this way CDC myDC; CBitmap myBitmap; .... MyFunction(myDC,myBitmap); Isn't a reference just a name ? And isn't a name just a pointer ? I don't get it. Thanks, Robert A. Yes. That is...

$ in cell reference?
Pardon me if this is a stupid question, I am an access programmer manipulating an excel spreadsheet with the object model. What is the function of the $ in the following cell entry?: =AVERAGE(S$3:S$33) Thanks for any help. Fred It's so when you copy or move your formula to other cells, the 3 and 33 won't change. The S colum will. So say this formula was in A1. If you copied it to B1, it would read: =AVERAGE(T$3:T$33) Likewise, if the $ were in front of the S, then the S's wouldn't change either when you copy the formula. It means that the cell r...

Multiple a Value to a cell while importing a wroksheet
Is there such a feature? What I need is to have column A1's value alway multiple by 100 in a worksheet, however, I don't know the range of n fo A1:A'n' , can Excel do such a multiple action while importating th worksheets -- slu ----------------------------------------------------------------------- sluk's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3665 View this thread: http://www.excelforum.com/showthread.php?threadid=56404 What determines the 'n'? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC...

Switching Between Showing Formula Or Actual Result In A Cell ?
Hello: Probably so simple, I'm embarrased to ask. I put in a formula such that in a cell there would be the SUM of a column of numbers. Pretty sure I did it correctly. When i look at the cell that should have the Sum, I see the formula. How do I switch between this cell showing the formular and/or the Sum ? Thanks, Bob you may have missed the = sign from in front of your formula "Robert11" wrote: > Hello: > > Probably so simple, I'm embarrased to ask. > > I put in a formula such that in a cell there would be the SUM of a column of > numbers. &...

"killing cells that are no longer in use"
Hi, My question: When I have a list of data of for example 20000 rows, then when I delete 10000 rows from the bottom, excel still seems to think that all 20000 cells are in use. I notice this when I use for example CTR+SHIFT+END (You can use this to select all cells with a value when you are standing on the first cell with a value). How can I let excel forget about these 10000 at the bottom as I am not using them anymore. This is important for me as these empty cells cause errors when I try to import excel sheets into SAP and Access. Thanks in advance! Robby -- VILLABILLA -------...

Changing cell alignment defaults.
Is there a way to change cell alignment defaults in Excel 2002? I get frustrated because the default alignment is at the "general" and "bottom" of the cell and I always prefer "top" and "left". Any ideas? Save styles to use in new workbooks Open a new blank workbook. Open the workbook that contains the styles you want to save. Copy the styles from your workbook to the blank workbook. How? Open the workbook that contains the styles you want to copy. Open the workbook you want to copy the styles to, and then click Style on the Format menu. C...

how do i use excel to refer to outlook?
I'm trying to pull contact information and other information from outlook and use in excel Try File-->Import and Export from Outlook. ************ Anne Troy www.OfficeArticles.com "chip_pyp" <chip_pyp@discussions.microsoft.com> wrote in message news:14CF4B2D-A361-4FA6-B41A-D965EA40D8BB@microsoft.com... > I'm trying to pull contact information and other information from outlook > and > use in excel ...

How can I unlock the grouping in Excel while locking cells?
When protecting a worksheet I would like to expand and contract groups while select cells are protected ...

How do I sort protected cells?
When I apply protection to my sheets, the prompt offers to allow sorting of protected cells, but even when I check that box, it won't work. I can get it to allow almost everthing else on that list of choices for protected cells, but no sorting....thoughts? Do you have any locked cells in the range you want sorted? Are you selecting just the unlocked cells that should be sorted? Stephen238 wrote: > > When I apply protection to my sheets, the prompt offers to allow sorting of > protected cells, but even when I check that box, it won't work. I can get it > to allow al...