Page 1 of 1

SOLVED: Cotton Has Another Excel Question

Posted: Thu Mar 29, 2012 3:16 pm
by Charles L. Cotton
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.

Re: Cotton Has Another Excel Question

Posted: Thu Mar 29, 2012 3:19 pm
by pbwalker
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.

Re: Cotton Has Another Excel Question

Posted: Thu Mar 29, 2012 3:21 pm
by Charles L. Cotton
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.
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.

Chas.

Re: Cotton Has Another Excel Question

Posted: Thu Mar 29, 2012 3:25 pm
by pbwalker
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?

Re: Cotton Has Another Excel Question

Posted: Thu Mar 29, 2012 3:34 pm
by leftyonly
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

Posted: Thu Mar 29, 2012 4:00 pm
by Keith B
If you want to set the whole cell reference to absolute, then highlight them and hit the F4 key.

Re: Cotton Has Another Excel Question

Posted: Thu Mar 29, 2012 4:29 pm
by Charles L. Cotton
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!
That's what I wound up doing, but in three steps:

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.

Re: Cotton Has Another Excel Question

Posted: Thu Mar 29, 2012 4:30 pm
by Charles L. Cotton
Keith B wrote:If you want to set the whole cell reference to absolute, then highlight them and hit the F4 key.
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.

That would have been even better than the search/replace although that went quickly.

Thanks,
Chas.

Re: Cotton Has Another Excel Question

Posted: Thu Mar 29, 2012 4:34 pm
by terryg
Keith B wrote:If you want to set the whole cell reference to absolute, then highlight them and hit the F4 key.
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.

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)

Re: Cotton Has Another Excel Question

Posted: Thu Mar 29, 2012 4:40 pm
by Charles L. Cotton
terryg wrote:
Keith B wrote:If you want to set the whole cell reference to absolute, then highlight them and hit the F4 key.
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.

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)
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.

Re: Cotton Has Another Excel Question

Posted: Fri Mar 30, 2012 3:03 pm
by terryg
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.
Ok, I see now ... that makes sense. Thanks for sharing the resolution.