TexasCHLforum.com

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.

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.

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.

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?

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!

If you want to set the whole cell reference to absolute, then highlight them and hit the F4 key.

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.

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.

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)

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.

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.