Relative and Absolute Reference to Cells in a Formula

Hi all,

I can't find how to do this...

I am working in worksheet Sheet2 and have inserted this formula in cell C3


=IF(B3=7,Sheet1!B49*Sheet1!B61,IF(B3=6,Sheet1!B48*Sheet1!B61,IF(B3<6,Sheet1!B47*Sheet1!B61,0))) 



the formula works OK.



Now, if I copy and paste the above formula to Sheet2!C4

the formula automatically changes to:

=IF(B4=7,Sheet1!B50*Sheet1!B62,IF(B4=6,Sheet1!B49*Sheet1!B62,IF(B4<6,Sheet1!B48*Sheet1!B62,0)))

so all references to cells are incremented of 1 unit.



My problem is that I want only the reference to the B column cells on 
Sheet2 to change automatically, so all other references to cells in the 
formula should be absolute, not relative.




So my formula should automatically change to

=IF(B4=7,Sheet1!B49*Sheet1!B61,IF(B3=6,Sheet1!B48*Sheet1!B61,IF(B3<6,Sheet1!B47*Sheet1!B61,0))) 


when I copy it into B4



and to

=IF(B5=7,Sheet1!B49*Sheet1!B61,IF(B3=6,Sheet1!B48*Sheet1!B61,IF(B3<6,Sheet1!B47*Sheet1!B61,0)))


when I copy it into B5, and so on.


Is there a way to tell excel to not incremented those cell references?


thanks
0
jimt
8/15/2007 12:48:49 PM
excel 39879 articles. 2 followers. Follow

2 Replies
485 Views

Similar Articles

[PageSpeed] 11

I repost as there was an error on the previous posting, the formula is 
copied to adjacent cells on the C column, not on the B column.

thanks



the formula works OK.



Now, if I copy and paste the above formula to Sheet2!C4

the formula automatically changes to:

=IF(B4=7,Sheet1!B50*Sheet1!B62,IF(B4=6,Sheet1!B49*Sheet1!B62,IF(B4<6,Sheet1!B48*Sheet1!B62,0)))

so all references to cells are incremented of 1 unit.



My problem is that I want only the reference to the B column cells on 
Sheet2 to change automatically, so all other references to cells in the 
formula should be absolute, not relative.




So my formula should automatically change to

=IF(B4=7,Sheet1!B49*Sheet1!B61,IF(B3=6,Sheet1!B48*Sheet1!B61,IF(B3<6,Sheet1!B47*Sheet1!B61,0)))

when I copy it into C4



and to

=IF(B5=7,Sheet1!B49*Sheet1!B61,IF(B3=6,Sheet1!B48*Sheet1!B61,IF(B3<6,Sheet1!B47*Sheet1!B61,0)))


when I copy it into C5, and so on.


Is there a way to tell excel to not incremented those cell references?


thanks
0
jimt
8/15/2007 12:55:59 PM
=IF(B3=7,Sheet1!B$49*Sheet1!B$61,IF(B3=6,Sheet1!B$48*Sheet1!B$61,IF(B3<6,Sheet1!B$47*Sheet1!B$61,0)))


-- 
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"jimt" <nospam> wrote in message 
news:eV$Jvqz3HHA.5980@TK2MSFTNGP04.phx.gbl...
>
> Hi all,
>
> I can't find how to do this...
>
> I am working in worksheet Sheet2 and have inserted this formula in cell C3
>
>
> =IF(B3=7,Sheet1!B49*Sheet1!B61,IF(B3=6,Sheet1!B48*Sheet1!B61,IF(B3<6,Sheet1!B47*Sheet1!B61,0)))
>
>
> the formula works OK.
>
>
>
> Now, if I copy and paste the above formula to Sheet2!C4
>
> the formula automatically changes to:
>
> =IF(B4=7,Sheet1!B50*Sheet1!B62,IF(B4=6,Sheet1!B49*Sheet1!B62,IF(B4<6,Sheet1!B48*Sheet1!B62,0)))
>
> so all references to cells are incremented of 1 unit.
>
>
>
> My problem is that I want only the reference to the B column cells on 
> Sheet2 to change automatically, so all other references to cells in the 
> formula should be absolute, not relative.
>
>
>
>
> So my formula should automatically change to
>
> =IF(B4=7,Sheet1!B49*Sheet1!B61,IF(B3=6,Sheet1!B48*Sheet1!B61,IF(B3<6,Sheet1!B47*Sheet1!B61,0)))
>
> when I copy it into B4
>
>
>
> and to
>
> =IF(B5=7,Sheet1!B49*Sheet1!B61,IF(B3=6,Sheet1!B48*Sheet1!B61,IF(B3<6,Sheet1!B47*Sheet1!B61,0)))
>
>
> when I copy it into B5, and so on.
>
>
> Is there a way to tell excel to not incremented those cell references?
>
>
> thanks 


0
bob.NGs1 (1661)
8/15/2007 12:58:34 PM
Reply:

Similar Artilces:

Using SUM + INDEX in array formula
I'm having problems getting an array formula like this to work: =SUM(INDEX(Data_Table,Row_Index_List,Col_Index_List)) Basically, I have two columns of numbers that represent the row and column indexes from a data table (ranges Row_Index_List and Col_Index_List). I want to look up the numbers in the corresponding row/column of range Data_Table, and return the sum of all returned values. More detail: Row_Index_List and Col_Index_List are columns of (let's say) 100 cells, and Data_Table is a 7*5 range on the same sheet. It looks like the combination of SUM(INDEX(..)) doe...

How do you copy a cell's content verses it's formula?
I have 2 cells and combined them into a third cell with the following formula... =a1&" "&b1. I was combining a person's first name (cell 1) with a person's second name (cell 2) so cell 3 included the first and second name. Now I want to copy and paste cell 3, but it copies the formula... I need to paste in the content (first and second name) not the formula. Hi Tammy, You could use a macro see JOIN macro on it's page http://www.mvps.org/dmcritchie/excel/join.htm not what you actually asked because if would change column A with the concatenated const...

Can I show a reference value with a string?
Hi All, For a single field, can I show a refernce value (ex: =B2+B3) and then follow up some strings? the final result looks like this: The price is 20 Thank you very much. Best regards, Boki. ="The price is "&B2+B3 -- goober ------------------------------------------------------------------------ goober's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19838 View this thread: http://www.excelforum.com/showthread.php?threadid=486137 If you want to use a format like currency you need to use the TEXT function ="The price is &q...

formatting cell numbers
I've tried everything I can think of and still can't get a cell to hold a whole number. I've tried the format number with no decimal, using general number, and custom. Yet it all returns less than whole numbers. Any ideas? Could a setting in MS Money cause this problem? On Thu, 2 Oct 2008 19:04:01 -0700, DRKENNE <DRKENNE@discussions.microsoft.com> wrote: >I've tried everything I can think of and still can't get a cell to hold a >whole number. I've tried the format number with no decimal, using general >number, and custom. Yet it all returns l...

Tricky question about pasting references
I have a group of cells that are right next to each other. Call these, A1-D1. The cells reference cells in another sheet, but the cells they are referencing are not necessarily adjacent. For example, A1-D1 will contain (in order) references to Worsheet!C3,Worsheet!D3, Worksheet!D3, and Worksheet!F3. I'd like to start a new worksheet where the arrangement of the references is maintained, except that I am now referencing a different worksheet where the referenced cells are moved to the right. For example, cells C3, D3, D3 and E3 are now NewWorksheet!E3, NewWorksheet!F3, NewWorksheet!F3...

CF to Color-Fill Row B:G w/Specific Text in One of the Cells
Hi All, I'm in Excel 07 and I have a worksheet which is populated from another worksheet, INDEX-formulas in all cells. Range is B2:G25. Column C gets populated with either M or F. I'm looking for a CF method so that if there's specifically an exact M in row C (not just an M in any word) that the whole row turns a color. The Fs need to do it too but change a different color and I'm guessing I'd just use the same formula but have it look for Fs and change the Fill color, right? Can someone please help me out with a formula for that? Please. Thank...

combining related data into one row of data
Access 2003 If I have a table like this with fields and data like this: Table1 Acct - Name ------ value1 - value2 - value3 123------Bob------------10-----------10-------10 123------Sue------------10-----------10-------10 How can I get a query result like this? Acct - Name ------ value1 - value2 - value3 123--- Bob, Sue------20------------20--------20 Any assistance greatly appreciated! RBolling On Aug 2, 4:54 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com> wrote: > There is a generic concatenate function with sample usage athttp://www.rogersaccesslibrary.com/OtherLibraries.asp...

How to avoid black shade fill on the from cell,when drag-and-drop
Please help me. I tried to drag data from one cell to the other, but the previuus cell is fill with black shade every time I transfer on excel. How to avoid it. Thanks ...

Entering array in single cell
I want to create an array in a single cell and use that as a lookup. I thought I'd found exactly what I need on Chip Pearson's site which describes precisely what I'm trying to do but when I follow the instrucions my lookup formula results in #REF! If I try and enter the array with Ctrl-Shft-Enter I get the "the formula you typed contains an error " message. I'm confused! I must be missing something really obvious. The info from Chip Pearson's site is below. I'd be grateful for an idiot's guide on exactly how to get this to work. Thanks a lot...

Target cell=range??
I am compiling a series of tables that calculate the occurences o multiple data sets that satisfy specific conditions. I must apply thes tables to ever changing source data (within the same worksheet). Th formulas that exist within the table are absolute cell references eg =SUMPRODUCT(($E$1355:$E$1978>=0)*($D$1355:$D$1978<=0.49)*($D$1355:$D$1978>=0)*($G$1355:$G$1978<=2.99)*($G$1355:$G$1978>=U1355)*($G$1355:$G$1978<=V1355)) is there a method by which the absolute cell reference could be entere in another target cell such that ($E$1355:$E$1978>=0) becomes (A1>=0) w...

convert equation to formula
Is there anyway to take an equation written in Equation editor and convert it to an Excel formula? You'll have to do that manually. And it is very interesting work. :) "DW in SF" <DW in SF@discussions.microsoft.com> wrote in message news:2FC66A71-338C-40CC-9B9C-32176388F3AF@microsoft.com... > Is there anyway to take an equation written in Equation editor and convert > it > to an Excel formula? I just Used the Equation Editor to record a Chemical Equation so I doubt that the editor can do this. However there is a "MATHFORMAT" that might do some ...

Combining cell values
I have a list which has companyname and contracts numbers in column A & B Example Company Name Contract Number AAA 888888 BBB 888088 BBB 888333 What I could like to do is to write a formula or a macro to combine all the contracts numbers for a company into a single cell for example AAA 888888 BBB 888088, 888333 Can this be done? Thanks Pls try this formulae in column C =A1&" "&B1 Note : " " is for spacing Rajkuma -- Message...

vlookup -- table_array as reference to named range
Dear geniuses, I have price sheets coming from several sources with the same format and products, but containing difference prices. I am trying to build a dataset (table) from these sources which I could then link to Access for querying. Given each price sheet source is a seperate worksheet in a workbook, I have given each sheet the named range of its pricing rate. For example, RATE_165 represents the pricing sheet which contains the prices for the "165" rate; RATE_180 represents the pricing sheet which contains the prices for the "180" rate. I want to be able to...

Indirect function
Hi, I struggle to create a macro with INDIRECT function that would jump to different cells. What cell I want to jump to depends on a currently selected cell. This means I need to put a relative reference into the function. Here is my function - I need to replace R1C1 expression with a realtive one (currently selected cell address with the same behavior as R1C1 expression). How can I do that? Application.Goto Reference:= _ "INDIRECT(CONCATENATE(R56C8,""!"",""a"",MATCH(R56C1,INDIRECT(CONCATENATE(R56 C8,1)),false)))" Whatever I do I get follo...

merge cells with a twist
A B C joe @ domain.com trying to merge cells in a row so that column a(joe) b(@) c(domain.com) and that result is joe@domain.com Thank you. =A1&B1&C1 -- Regards, Peo Sjoblom (No private emails please) "jd" <jdumont@@novuscom.net> wrote in message news:uaAWelbgFHA.3436@tk2msftngp13.phx.gbl... >A B C > joe @ domain.com > > trying to merge cells in a row so that column a(joe) b(@) c(domain.com) > and > that result is joe@domain.com > Thank you. > > =hyper...

group cells....
Hi I've following information wanted to group by first column and show the sum of quantities: a b c ---- ----- ------ 123 10.8 123 20.2 123 44 124 2 124 11 124 45 The desired result: a b c ---- ----- ------ 123 10.8 75 -> sum(10.8+20.2+44) 123 20.2 123 44 124 2 58 -> sum(2+11+45+0) 124 11 124 45 124 0 note: I don't want to write sum(a1:a3) , ... because it depends on the number of rows has the same value. any help would greatly appricieted. -- Using M2, Opera's revolutionary e-mail c...

show first few letters of a column in another cell
I know how to use left to show the first character of a particular column in another column. What I need to do is show the first seven characters. For Instance: Column A has a value of "McDonald" I want Column B to show "McDonal" Thanks. =LEFT(A1,7) if A1 is your reference cell. -- HansM "Glenn" <nospam@yahoo.com> wrote in message news:O1l1Z7TOFHA.1040@TK2MSFTNGP12.phx.gbl... >I know how to use left to show the first character of a particular column >in > another column. > > What I need to do is show the first seven characters. >...

Excel formula #10
In one cell I have hours mins i.e. 7:30. I would like the=20 formula for another cell to calculate cost of this amount=20 of time, for example 7:30 @ =A330/hour. How would I express=20 that as a formula. I have tried straight forward 7:30*30, doesn't work. Any=20 suggestions please? Thank you. Hi Jean try A1*24*30 where A1 stores your time.<format the resulting cell as number HTH Frank jean wrote: > In one cell I have hours mins i.e. 7:30. I would like the > formula for another cell to calculate cost of this amount > of time, for example 7:30 @ �30/hour. How would I ex...

relative path...
Hello, Using VC++6, I have created an MFC application. My .exe utilizes an outside folder named 'MyFolder'. Presently, I have used the following code in my .exe to access 'MyFolder': CString InitialPath="C:\\Documents and Settings\\Main\\Desktop\ \MyFolder\\"; 'MyFolder' is located in the same folder as my '.exe' What relative path could I use so '.exe' can access 'MyFolder'? Thanks in advance, RABMissouri2007 "RAB" <rabmissouri@yahoo.com> ha scritto nel messaggio news:1188483407.448383.320730@g4g2000hsf.goo...

How do i get excel to format a cell for a negative result.
Hi, Can someone please tell me how i can get excel to format the cell when the result is negative. eg 2 - 3 = -1 therefore the cell would turn red. in menu format-cells-number- choose whichever format for negative n;umber Cisco <Cisco@discussions.microsoft.com> wrote in message news:E36E8CE1-5FDC-42EF-9BCC-C8E3A9768EB6@microsoft.com... > Hi, > > Can someone please tell me how i can get excel to format the cell when the > result is negative. > > eg 2 - 3 = -1 therefore the cell would turn red. Hi Cisco Select the range where you want this t apply, click on Fo...

How to add column(s) that show Yes or No indicating whether that record came from a related table
I had to merge three tables together, and now a request has been made to show (in the merged flat file) in which report the data exists. So I will be adding three columns, and there needs to be a streaming Yes or No in each column, indicating whether the Person's name matches to each file. Is there a way to do this? I am stumped, but mainly b/c I am new to Access and am learning how to connect some of these queries to result in the desired output. Any suggestions would be appreciated. > I had to merge three tables together Why? As in why would you want/need to do that? That is comp...

inserting $ at every cell mentioned in a formula
Hi! I have several long formula's in different cells and I would like t add a $ sign to every cell mentioned in those formula's. My question i how can I do this in an easy way, manually filling in these signs wil take ages, please help! Regards -- Message posted from http://www.ExcelForum.com Hi Villabilla, > I have several long formula's in different cells and I would like to > add a $ sign to every cell mentioned in those formula's. My question is > how can I do this in an easy way, manually filling in these signs will > take ages, please help! > Maybe w...

contents in cells are not visible when word wrap is selected
It appears the profile or standard template for excel has been changed on my machine. Spreadsheets I worked on earlier this week that contained cells with word wrap as well as new spreadsheets I create have the same problem... When using word wrap, the contents of the cell "disapear" but are still visible in the formula bar. I've removed all formating and unchecked any cell formats with no success. I've checked all the file, edit, view, ect. settings for anything that appears to be unusual. Any sugestions would be appreciated... my IT department cannot look at my ...

color formatting of partial text of a cell in excel in bulk
i want to make a cell with multicolor text......... how to do it in bulk(e.g as we do it in replace all) If the text displayed in the cell is the result of a formula in that cell, you cannot have more than one color. If your data is a pure text entry, you can have multi-colored text, but you will need to use a macro to do more than one cell at a time. If your cell entries are second condition (pure text), then give us more information about what you want to do. Rick "chetan" <nishuu.chetan@gmail.com> wrote in message news:8c6b5711-0808-4f53-8462-94b08d180d91@z16g2000...

cell.value comply with cell.value before and cell.value after
Hi Expert, In column C which is sorted based on column D, groups of words emerge. Occasionally a deviant word occurs within such a group of uniform words, eg.: row column C 1 Word 2 Word 3 Word 4 ord 5 Word 6 Word 7 From 8 From etc. etc. Is there some way I can make 'ord' like 'Word' and loop through the whole of Column C to fix similar situations. The condition is that ord lies between two similar words, 'Word'. When row 7 starts with a new group of Words, eg From, this From word will be left untouched. Woul...