| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying formulas to other cells. Keeping references w/o $ sign.
How do I copy formulas from a large group of cells to another group of cells and have them all keep the references to the same cells? (It normally changes the referneces to cells that have the same position in relation to the former cell) I don't want to go in and change all of the references, adding the $ sign. -- GregP1962 ------------------------------------------------------------------------ GregP1962's Profile: http://www.excelforum.com/member.php...o&userid=33063 View this thread: http://www.excelforum.com/showthread...hreadid=535102 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copying formulas to other cells. Keeping references w/o $ sign.
I usually try to put the $ before dragging the cells, but when I want a copy
of the data somewhere that refers back, I to the first cell where I want the data, enter "=" and click the first cell of the area where I want to copy from. Then drag to expand to the whole area. "GregP1962" wrote: How do I copy formulas from a large group of cells to another group of cells and have them all keep the references to the same cells? (It normally changes the referneces to cells that have the same position in relation to the former cell) I don't want to go in and change all of the references, adding the $ sign. -- GregP1962 ------------------------------------------------------------------------ GregP1962's Profile: http://www.excelforum.com/member.php...o&userid=33063 View this thread: http://www.excelforum.com/showthread...hreadid=535102 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copying formulas to other cells. Keeping references w/o $ sign.
You can do: Copy PasteSpecial PasteLink It will not carry the formulas in new cells but since it links your new cells to the copied cells, it will give the same results as if the formulas are there. -- renegan ------------------------------------------------------------------------ renegan's Profile: http://www.excelforum.com/member.php...o&userid=10450 View this thread: http://www.excelforum.com/showthread...hreadid=535102 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copying formulas to other cells. Keeping references w/o $ sign.
OK, The paste special, (using the "values" option) just moved the result of the formula without moving the formula. So, any future changes will not show in the copied cell. If I use the = sign in the cell I am copying to, it just makes a reference to that cell. What I want is to copy the same formulas with data from the SAME cells as the formulas I am copying from without having to go into every formula to add th $ sign. -- GregP1962 ------------------------------------------------------------------------ GregP1962's Profile: http://www.excelforum.com/member.php...o&userid=33063 View this thread: http://www.excelforum.com/showthread...hreadid=535102 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copying formulas to other cells. Keeping references w/o $ sign.
Didn't Gary's Student's suggestion work for you?
First select the cells Then use find/replace to change all = into x= This will change all the formulae into text strings. Select the range to copy Edit|replace what: = (equal sign) with: $$$$$ (I like this better than x= replace all copy and paste these text strings to where you want them. Then select that pasted range edit|replace what: $$$$$ with: = replace all Now the text strings are converted back to formulas. Don't forget to fix the original range, too. GregP1962 wrote: OK, The paste special, (using the "values" option) just moved the result of the formula without moving the formula. So, any future changes will not show in the copied cell. If I use the = sign in the cell I am copying to, it just makes a reference to that cell. What I want is to copy the same formulas with data from the SAME cells as the formulas I am copying from without having to go into every formula to add th $ sign. -- GregP1962 ------------------------------------------------------------------------ GregP1962's Profile: http://www.excelforum.com/member.php...o&userid=33063 View this thread: http://www.excelforum.com/showthread...hreadid=535102 -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copying formulas to other cells. Keeping references w/o $ sign.
But, how do you "Then use find/replace to change all = into x="? Selecting edit/find/replace give a very confusing dialog box. If I had all afternoon, I'd try to figure that box out by trial and error. -- GregP1962 ------------------------------------------------------------------------ GregP1962's Profile: http://www.excelforum.com/member.php...o&userid=33063 View this thread: http://www.excelforum.com/showthread...hreadid=535102 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copying formulas to other cells. Keeping references w/o $ sign.
Select the range that should be adjusted.
Hit Ctrl-h (or edit|Replace) In the "find what" box, type an equal sign. In the "replace with" box, type $$$$$ click replace all. Remember how you got it to work. You'll have to do it 2 more times to reverse what you did. GregP1962 wrote: But, how do you "Then use find/replace to change all = into x="? Selecting edit/find/replace give a very confusing dialog box. If I had all afternoon, I'd try to figure that box out by trial and error. -- GregP1962 ------------------------------------------------------------------------ GregP1962's Profile: http://www.excelforum.com/member.php...o&userid=33063 View this thread: http://www.excelforum.com/showthread...hreadid=535102 -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copying formulas to other cells. Keeping references w/o $ sign.
OK, that didn't work. I think the reason is that the forumlas have some $ in them. When I try to change things back to the =, the ones that are supposed to be $ get changed to =. -- GregP1962 ------------------------------------------------------------------------ GregP1962's Profile: http://www.excelforum.com/member.php...o&userid=33063 View this thread: http://www.excelforum.com/showthread...hreadid=535102 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copying formulas to other cells. Keeping references w/o $ sign.
Can you close your workbook without saving?
If yes, then do that and reopen the workbook. Try it again and be very careful what you type into each of those boxes in the Edit|Replace dialog. If it doesn't work, then post back exactly what you typed into each of the boxes. This technique will work when you do it correctly. GregP1962 wrote: OK, that didn't work. I think the reason is that the forumlas have some $ in them. When I try to change things back to the =, the ones that are supposed to be $ get changed to =. -- GregP1962 ------------------------------------------------------------------------ GregP1962's Profile: http://www.excelforum.com/member.php...o&userid=33063 View this thread: http://www.excelforum.com/showthread...hreadid=535102 -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copying formulas to other cells. Keeping references w/o $ sign.
OK, I got it. I used the find/replace to change the column names in the new location. Thanks, I had never used the find/replace function. -- GregP1962 ------------------------------------------------------------------------ GregP1962's Profile: http://www.excelforum.com/member.php...o&userid=33063 View this thread: http://www.excelforum.com/showthread...hreadid=535102 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copying formulas to other cells. Keeping references w/o $ sign.
Now that you've used the Edit|replace dialog, you may want to try the other
suggestion. Save your workbook first. If you screw it up, you can close without saving and do no harm. GregP1962 wrote: OK, I got it. I used the find/replace to change the column names in the new location. Thanks, I had never used the find/replace function. -- GregP1962 ------------------------------------------------------------------------ GregP1962's Profile: http://www.excelforum.com/member.php...o&userid=33063 View this thread: http://www.excelforum.com/showthread...hreadid=535102 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
| copying formulas in vba | Excel Discussion (Misc queries) | |||
| How do I copy only cells with formulas in another row? | Excel Worksheet Functions | |||
| Stoping users pasting formulas into cells | Excel Worksheet Functions | |||
| In Exel 2000, stop the blank cells (with formulas) from printing. | Excel Discussion (Misc queries) | |||
Linear Mode
