Moving spreadsheets with $ 1

[Lcubed]

Can anyone tell me how to copy a spreadsheet containing cell designations such as $a$1, to a new location without dragging those cell designations along?

Say, for example, I want to copy the sheet to a new location 12 lines down, and I want cell $a$1 to become cell $a$12, in which I will put a new value to be used in the moved spreadsheet. As it is, I move the sheet and the cell remains $a$1, so I have to change that designation to $a$12 in every location where it is used.

 

[SacreBleu]

Just remove the $ in front of the 12

 

[SacreBleu]

Ooops - sorry ...in front of the 1

 

[trainguy]

You can handily remove all $ characters using the find/replace command on a range of cells.

Find: $
Replace: (nothing)

tg

 

[brengine]

And/or you could find all of the $a$1 replace to $a$12 on a range of cells.

Ken

 

[flamby]

Or just insert blank new rows on the top.

Ciao.

 

[electricpete]

I wanted to ask Lcubed if he is aware of the difference between aboslute cell references ($a$1) and relative cell references (a1)

If he is familiar with that, I apologize for making assumptions about the question.

If he is not familiar with that, it is an important concept for excel and we would be glad to explain.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[ColourfulFigsnDiags]

$a$1 type of reference is normally used for fixed parameters (eg molecular weight of water) or things that wont change (I say typically- but not always!).

Often, therefore, it is useful to actually name the cell rather than giving a cell reference.

To name a cell just click the little box which says what cell you are in at the top left (beside the formula line) ... enter in a name (eg "mw-water") and press return.

Then every time you want to use the MW of water you would just type "=mw-water" into the cell.

If you then insert cells above a1, the named cell will move too... a very handy thing to do, and also makes your spreadsheets that much easier to read ... eg, which is easier to understand when calculating the reynolds number ... "=pipeD*WaterVel*WaterDen/WaterVis" or "=$A$1*$B$4*$B$3/$R$12" ? the named cells also propogate throughout the work book and can be edited/deleted under the >>Insert>>Name>>Define ... menu

 

[Lcubed]

Thanks all for the helpful input.

I oversimplified my question somewhat; in reality there are a large number of rows each containing several cells which, by use of multiple dollar signs, reference absolute cells in other columns and rows. What I do now is use the 'find and replace' tool to remove the second $ from each cell in the top row. That is $A$1 becomes $A1. Then I move the entire spreadsheet to its new location-either straight down or to a new worksheet where the columns will still be the same-then I replace the dollar signs which I removed; then I copy the top row down which makes everything OK. The tedious part is replacing the dollar signs.

I do understand the difference between absolute and relative cell designations, and the need for this feature, but it would be nice if it could be disabled for purposes of copy/paste and then reactivated somehow in the pasted copy.

Also, of course, I know how to remove all the dollar signs and move to a different set of rows AND columns; that, however isn't usually necessary.

I'm going to try all of the solutions offered, and especially the one by ColourfulFigsnDiags, as it sounds like an elegant solution. Many thanks again, and if my discussion above prompts any new ideas, please let me know.

Regards.

 

[brengine]

Another choice:
-Save a copy of your Excel file.
-In the copied file cut and paste your cells rather than copy and paste to new desired location(s).
-Once you're done with the cutting, then copy/paste the new layout into your original worksheet. Just make sure that the cell addresses you copy from exactly match the cells you paste to...or you'll really get confused. (-:

Ken

 

[SacreBleu]

Lcubed,
This may help - I haven't checked it out specifically for your situation.

http://asaputilities.com/

 

[Panars]

ASAP utilities (mentioned above) includes a macro to convert absolute references to relative references. However, it also lets you convert to absolute rows and relative columns, or relative rows and absolute columns. So a reference to $A$1 can be converted to either $A1 or A$1.

 

[danberry]

Maybe this will help. Search and replace = with say "equal" then copy your cells. Excell will not change the reference since they are not formulas. Then do the reverse and replace "equal" with =