Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Moving spreadsheets with $

Moving spreadsheets with $

Moving spreadsheets with $

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.  

RE: Moving spreadsheets with $

Just remove the $ in front of the 12

RE: Moving spreadsheets with $

Ooops - sorry ...in front of the 1

RE: Moving spreadsheets with $

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

Find:     $
Replace:   (nothing)


RE: Moving spreadsheets with $

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


RE: Moving spreadsheets with $

Or just insert blank new rows on the top.


RE: Moving spreadsheets with $

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.

RE: Moving spreadsheets with $

$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

RE: Moving spreadsheets with $

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.


RE: Moving spreadsheets with $

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.


RE: Moving spreadsheets with $

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


RE: Moving spreadsheets with $

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.

RE: Moving spreadsheets with $

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 =

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close