| Author | Thread |
|
|
08/21/2007 06:58:18 PM · #1 |
DPC...the go-to guys! (and gals)
OK, I'm building a spreadsheet. To save time, I had a row of formulas which did operations within the row (like adding and diving, etc). I copied these for multiple rows and the formulas updated (because they were relative formulas) so they worked in their own row. What I would like to do is now change each formula to an absolute formula. Do I have to go through and manually put the "$" in? When I hit F4, the formula actually reverts to an absolute form of the original row (so the row 5 formula works off of the original formula's row 4 numbers). My problem is I need to use the spreadsheet to paste a chunk of data that then needs to be spaced appropriately by moving rows up or down. I don't want the formulas to follow this moving of cells.
Clear as mud? Any opinions on what to do?
|
|
|
|
08/21/2007 06:59:57 PM · #2 |
sounds like yes, you do want to change to absolute cell references. (either $C5 to fix the row or C$5 to fix the column, or $C$5 to fix both)
Or you could compute the references on the fly I suppose. |
|
|
|
08/21/2007 07:04:54 PM · #3 |
How many formulas do you have to change? If you have to change many, you can do this:
- Select all the formulas (or the portion that works best, see below)
- Use Edit/Replace All and replace, for instance, "C5" with $C$5".
- Repeat until you've replaced all of the cases you need to.
It's a little "brute force," but can be faster than changing individual formulas.
Message edited by author 2007-08-21 19:05:03. |
|
|
|
08/21/2007 07:10:17 PM · #4 |
If the reference is staying locked, ie $C$5, change the first one, highlight the cell, Ctrl C, highlight all the destination cells, Ctrl V.
That should copy the first formula (after it's changed) and paste it into the remaining cells. No individual formula changing required!
After rereading there are a couple of ways to do it. Macros, not necessarily the best way. Depending on the amount of data you can leave several columns/rows just for the data, the next set of columns would go grab the data referenced to the cells. When you move the data around the formulas remain untouched and update the results of the data move.
Message edited by author 2007-08-21 19:16:16. |
|
|
|
08/21/2007 07:17:12 PM · #5 |
| copy the cell info to a new spreadsheet (or new cells) & copy the data only (not the formulas) |
|
Home -
Challenges -
Community -
League -
Photos -
Cameras -
Lenses -
Learn -
Help -
Terms of Use -
Privacy -
Top ^
DPChallenge, and website content and design, Copyright © 2001-2025 Challenging Technologies, LLC.
All digital photo copyrights belong to the photographers and may not be used without permission.
Current Server Time: 09/13/2025 01:30:50 AM EDT.
