SOLVED: Cotton Has Another Excel Question
-
Topic author - Site Admin
- Posts in topic: 5
- Posts: 17787
- Joined: Wed Dec 22, 2004 9:31 pm
- Location: Friendswood, TX
- Contact:
SOLVED: Cotton Has Another Excel Question
I have a spreadsheet I use to set up squads for our IDPA matches. One sheet has a 140 rows with 5 columns, 3 of which reference cells on another sheet. I need to convert these sells to absolute (I think that's the term) cell references. I know I can do that by adding "$" to the column reference and to the row reference, but that means I have to manually add 1680 dollar signs!! (One column has an If/Then statement with 4 cell references.)
Is there a quick way to convert cell references to absolute references? PLEASE tell me there is!
Thanks,
Chas.
Is there a quick way to convert cell references to absolute references? PLEASE tell me there is!
Thanks,
Chas.
-
- Senior Member
- Posts in topic: 2
- Posts: 3032
- Joined: Thu May 01, 2008 10:12 am
- Location: Northern Colorado
Re: Cotton Has Another Excel Question
If I understand the question correctly, all you need to do is highlight the cells (or row/column) you want set for currency, right click and choose format cells, and choose the number tab. Under there, choose currency.
Let me know if I understood the question correctly.
Let me know if I understood the question correctly.
*NRA Endowment Member* | Veteran
Vote Adam Kraut for the NRA Board of Directors - http://www.adamkraut.com/
Vote Adam Kraut for the NRA Board of Directors - http://www.adamkraut.com/
-
Topic author - Site Admin
- Posts in topic: 5
- Posts: 17787
- Joined: Wed Dec 22, 2004 9:31 pm
- Location: Friendswood, TX
- Contact:
Re: Cotton Has Another Excel Question
I wish that was it! I'm trying to make dynamic cell references absolute/static. You can do that by adding a dollar sign in front of the column and row references line this: $A$1. That way when the sheet is sorted or otherwise manipulated, the value returned from the other sheet will always be the same.pbwalker wrote:If I understand the question correctly, all you need to do is highlight the cells (or row/column) you want set for currency, right click and choose format cells, and choose the number tab. Under there, choose currency.
Let me know if I understood the question correctly.
Chas.
-
- Senior Member
- Posts in topic: 2
- Posts: 3032
- Joined: Thu May 01, 2008 10:12 am
- Location: Northern Colorado
Re: Cotton Has Another Excel Question
Ahh ok...out of my scope of expertise. lol
Maybe http://www.digdb.com/" onclick="window.open(this.href);return false; might have a tool that can do it?
Maybe http://www.digdb.com/" onclick="window.open(this.href);return false; might have a tool that can do it?
*NRA Endowment Member* | Veteran
Vote Adam Kraut for the NRA Board of Directors - http://www.adamkraut.com/
Vote Adam Kraut for the NRA Board of Directors - http://www.adamkraut.com/
Re: Cotton Has Another Excel Question
The first thought that came to me was using the search and replace. Use it wisely when replacing a single character, such as row "B" with row "$B". This is not ideal, but could save you alot of time...experiment with and see how you do!
Re: Cotton Has Another Excel Question
If you want to set the whole cell reference to absolute, then highlight them and hit the F4 key.
Keith
Texas LTC Instructor, Missouri CCW Instructor, NRA Certified Pistol, Rifle, Shotgun Instructor and RSO, NRA Life Member
Psalm 82:3-4
Texas LTC Instructor, Missouri CCW Instructor, NRA Certified Pistol, Rifle, Shotgun Instructor and RSO, NRA Life Member
Psalm 82:3-4
-
Topic author - Site Admin
- Posts in topic: 5
- Posts: 17787
- Joined: Wed Dec 22, 2004 9:31 pm
- Location: Friendswood, TX
- Contact:
Re: Cotton Has Another Excel Question
That's what I wound up doing, but in three steps:leftyonly wrote:The first thought that came to me was using the search and replace. Use it wisely when replacing a single character, such as row "B" with row "$B". This is not ideal, but could save you alot of time...experiment with and see how you do!
1. Search for "=" and replace it with " =" (with a leading space) to convert formulas to text; (Otherwise search/replace won't work.)
2. Search and replace "$A" with "$A$" (for each column letter);
3. Search for " =" and replace it with "=" to convert text back to formulas.
That worked much better!!
Chas.
-
Topic author - Site Admin
- Posts in topic: 5
- Posts: 17787
- Joined: Wed Dec 22, 2004 9:31 pm
- Location: Friendswood, TX
- Contact:
Re: Cotton Has Another Excel Question
I tried that and it didn't work. I'm running Excel 2003 one one machine (the one I used) and Excel 2010 on the others.Keith B wrote:If you want to set the whole cell reference to absolute, then highlight them and hit the F4 key.
That would have been even better than the search/replace although that went quickly.
Thanks,
Chas.
-
- Senior Member
- Posts in topic: 2
- Posts: 1719
- Joined: Sat Jul 03, 2010 12:37 pm
- Location: Alvin, TX
Re: Cotton Has Another Excel Question
Wow Keith, I wasn't aware of that shortcut ... pretty cool. But it does look like you have to be editing the cell, so it would still be one at a time.Keith B wrote:If you want to set the whole cell reference to absolute, then highlight them and hit the F4 key.
Mr. Cotton,
Generally when I have used absolute cell references, it is so that I can copy either down or across and NOT have the cell advance. Not having seen your sheet, I can't say for certain what is being setup. But you should be able to set the correct parts of the cell address off with the $ on the first cell and then drag the bottom corner to copy. Does that make sense?
So, the following formula:
=A1+$B2+C$3+$D$4
would become:
=A2+$B3+C$3+$D$4 when dragged down (or use Ctrl-D)
and would become:
=B1+$B2+D$3+$D$4 when dragged to the right (or use Ctrl-R)
... this space intentionally left blank ...
-
Topic author - Site Admin
- Posts in topic: 5
- Posts: 17787
- Joined: Wed Dec 22, 2004 9:31 pm
- Location: Friendswood, TX
- Contact:
Re: Cotton Has Another Excel Question
Overall that's how I did it. When creating the additional sheet, I needed the column to be absolute (ex. $A) but the row needed to by dynamic so I could copy the first row twenty times. I did that for each of 7 squads. However, I ultimately needed all cell references to be absolute so I can copy them for sorting into alphabetical order and to convert from formulas to values for printing. That means I need the cell references to be absolute to preserve the master spreadsheet.terryg wrote:Wow Keith, I wasn't aware of that shortcut ... pretty cool. But it does look like you have to be editing the cell, so it would still be one at a time.Keith B wrote:If you want to set the whole cell reference to absolute, then highlight them and hit the F4 key.
Mr. Cotton,
Generally when I have used absolute cell references, it is so that I can copy either down or across and NOT have the cell advance. Not having seen your sheet, I can't say for certain what is being setup. But you should be able to set the correct parts of the cell address off with the $ on the first cell and then drag the bottom corner to copy. Does that make sense?
So, the following formula:
=A1+$B2+C$3+$D$4
would become:
=A2+$B3+C$3+$D$4 when dragged down (or use Ctrl-D)
and would become:
=B1+$B2+D$3+$D$4 when dragged to the right (or use Ctrl-R)
Thanks,
Chas.
-
- Senior Member
- Posts in topic: 2
- Posts: 1719
- Joined: Sat Jul 03, 2010 12:37 pm
- Location: Alvin, TX
Re: Cotton Has Another Excel Question
Ok, I see now ... that makes sense. Thanks for sharing the resolution.Charles L. Cotton wrote:Overall that's how I did it. When creating the additional sheet, I needed the column to be absolute (ex. $A) but the row needed to by dynamic so I could copy the first row twenty times. I did that for each of 7 squads. However, I ultimately needed all cell references to be absolute so I can copy them for sorting into alphabetical order and to convert from formulas to values for printing. That means I need the cell references to be absolute to preserve the master spreadsheet.
Thanks,
Chas.
... this space intentionally left blank ...