Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy & Paste Forumla - but reference cell is changing
I am trying to copy and paste a large section of one worksheet into another
but the reference cell in the formula keeps changing. How can I paste it so that the reference cell remains the same regardless of where I paste it? i.e. Cell B15 in Sheet 1 has the formula =IF(model!AA25<"",model!AA25,model!Z25). When I paste this into cell C8 in Sheet 2, the formula changes to =IF(model!AB18<"",model!AB18,model!AA18) but I want it to still reference model!AA25¦ |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy & Paste Forumla - but reference cell is changing
hi
in xl help, look up absolute references vs. relative references. Relative references varies depending on where you paste them. aboslute references do not vary. to make a relative reference absolute, put dollar signs in from fo the column and row reference i.e. instend of A1, put $A$1. regards FSt1 "Andy" wrote: I am trying to copy and paste a large section of one worksheet into another but the reference cell in the formula keeps changing. How can I paste it so that the reference cell remains the same regardless of where I paste it? i.e. Cell B15 in Sheet 1 has the formula =IF(model!AA25<"",model!AA25,model!Z25). When I paste this into cell C8 in Sheet 2, the formula changes to =IF(model!AB18<"",model!AB18,model!AA18) but I want it to still reference model!AA25¦ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy & Paste Forumla - but reference cell is changing
Yeah, aware of absolute references, but the problem is that a lot of dragging
and forumla copying was needed in Sheet 1 and hence I never set the formulae up as absolute... "FSt1" wrote: hi in xl help, look up absolute references vs. relative references. Relative references varies depending on where you paste them. aboslute references do not vary. to make a relative reference absolute, put dollar signs in from fo the column and row reference i.e. instend of A1, put $A$1. regards FSt1 "Andy" wrote: I am trying to copy and paste a large section of one worksheet into another but the reference cell in the formula keeps changing. How can I paste it so that the reference cell remains the same regardless of where I paste it? i.e. Cell B15 in Sheet 1 has the formula =IF(model!AA25<"",model!AA25,model!Z25). When I paste this into cell C8 in Sheet 2, the formula changes to =IF(model!AB18<"",model!AB18,model!AA18) but I want it to still reference model!AA25¦ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy & Paste Forumla - but reference cell is changing
hi,
then you have a problem. without absolute references, the formulas will paste as relative references. no way around that. sorry. FSt1 "Andy" wrote: Yeah, aware of absolute references, but the problem is that a lot of dragging and forumla copying was needed in Sheet 1 and hence I never set the formulae up as absolute... "FSt1" wrote: hi in xl help, look up absolute references vs. relative references. Relative references varies depending on where you paste them. aboslute references do not vary. to make a relative reference absolute, put dollar signs in from fo the column and row reference i.e. instend of A1, put $A$1. regards FSt1 "Andy" wrote: I am trying to copy and paste a large section of one worksheet into another but the reference cell in the formula keeps changing. How can I paste it so that the reference cell remains the same regardless of where I paste it? i.e. Cell B15 in Sheet 1 has the formula =IF(model!AA25<"",model!AA25,model!Z25). When I paste this into cell C8 in Sheet 2, the formula changes to =IF(model!AB18<"",model!AB18,model!AA18) but I want it to still reference model!AA25¦ |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy & Paste Forumla - but reference cell is changing
You could copy the formula from B15 of Sheet1 into B15 of Sheet2, then
move (cut/paste) it to C8. This also applies if you have a block of cells relative to B15. Alternatively, you could try to make the cell references in Sheet1 into absolute using Find & Replace - highlight the cells, then CTRL-H and: Find What: !AA Replace with: !$AA$ Replace All and again with !Z, but it depends how variable your formulae are as to how easy this will be - you might have to apply it too many times to make it worthwhile. Hope this helps. On Sep 27, 10:40 am, Andy wrote: Yeah, aware of absolute references, but the problem is that a lot of dragging and forumla copying was needed in Sheet 1 and hence I never set the formulae up as absolute... "FSt1" wrote: hi in xl help, look up absolute references vs. relative references. Relative references varies depending on where you paste them. aboslute references do not vary. to make a relative reference absolute, put dollar signs in from fo the column and row reference i.e. instend of A1, put $A$1. regards FSt1 "Andy" wrote: I am trying to copy and paste a large section of one worksheet into another but the reference cell in the formula keeps changing. How can I paste it so that the reference cell remains the same regardless of where I paste it? i.e. Cell B15 in Sheet 1 has the formula '=IF(model!AA25<"",model!AA25,model!Z25)'. When I paste this into cell C8 in Sheet 2, the formula changes to '=IF(model!AB18<"",model!AB18,model!AA18)' but I want it to still reference 'model!AA25'...- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy & Paste Forumla - but reference cell is changing
On Sep 27, 5:23 am, Pete_UK wrote:
You couldcopytheformulafrom B15 of Sheet1 into B15 of Sheet2, then move (cut/paste) it to C8. This also applies if you have a block of cells relative to B15. Alternatively, you could try to make the cell references in Sheet1 intoabsoluteusing Find & Replace - highlight the cells, then CTRL-H and: Find What: !AA Replace with: !$AA$ Replace All and again with !Z, but it depends how variable your formulae are as to how easy this will be - you might have to apply it too many times to make it worthwhile. Hope this helps. On Sep 27, 10:40 am, Andy wrote: Yeah, aware ofabsolutereferences, but the problem is that a lot of dragging and forumla copying was needed in Sheet 1 and hence I never set the formulae up asabsolute... "FSt1" wrote: hi in xl help, look upabsolutereferences vs. relative references. Relative references varies depending on where youpastethem. aboslute references do not vary. to make a relativereferenceabsolute, put dollar signs in from fo the column and rowreferencei.e. instend of A1, put $A$1. regards FSt1 "Andy" wrote: I am trying tocopyandpastea large section of one worksheet into another but thereferencecell in theformulakeeps changing. How can Ipasteit so that thereferencecell remains the same regardless of where Ipasteit? i.e. Cell B15 in Sheet 1 has theformula '=IF(model!AA25<"",model!AA25,model!Z25)'. When Ipastethis into cell C8 in Sheet 2, theformulachanges to '=IF(model!AB18<"",model!AB18,model!AA18)' but I want it to stillreference'model!AA25'...- Hide quoted text - - Show quoted text - Pete...you're first suggestion is brilliant...so simple! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy-paste started misbehaving (cell reference wrong) | Excel Discussion (Misc queries) | |||
Using cell reference and wildcards in a forumla | Excel Discussion (Misc queries) | |||
Reference an identical cell on a different page using copy/paste? | Excel Worksheet Functions | |||
HOW DO I COPY FORMULA WITHOUT CHANGING CELL REFERENCE | Excel Discussion (Misc queries) | |||
Is there a forumla to link an absolute cell reference in multiple. | Excel Worksheet Functions |