SOLVED: Cotton Has Another Excel Question

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

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

[Charles L. Cotton]

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.

[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?

[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!

[Keith B]

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

[Charles L. Cotton]

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.

[Charles L. Cotton]

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.

[terryg]

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)

[Charles L. Cotton]

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.

[terryg]

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.