Absolute Reference

**haimd** · 04-23-2015, 01:43 PM

Hi,

I face the following problem in Excel:

I entered a reference in one cell, let's say D1, to cell A1
When I cut and paste cell A1 to other location, the reference in D1 changes also to the new place (e.g. C1).
Setting the reference to absolute form, i.i. $D$1 did not help.
I need that the reference in D1 will remain the same, i.e. A1, does not matter what happens to the cell (copy, cut etc.)

Thanks,

Haim

**FDibbins** · 04-23-2015, 01:47 PM

Hi, welcome to the forum

If you absolute a reference, it remains fixed, so Im not sure what you are doing, but =$D$1 will stay =$D$1 no matter where you cut/paste or copy/paste

The only time it will change, is if you insert rows or columns

**Arkadi** · 04-23-2015, 01:56 PM

Putting either =$A$1 or =A1 in D1 will still change to =C1 if you cut/paste A1 to C1. (I didn't think that was the case, but in my test it did)
But if in D1 you put =INDIRECT("A1") then it will always stay A1

**haimd** · 04-23-2015, 02:33 PM

Hi Arkadi,

Your answer solved my problem !

Thanks a lot

Haim

**Nisha Dhawan** · 04-23-2015, 02:04 PM

@haimd - if you cut paste the cell which contains formula, the reference will never get changed but if you cut paste your reference of the formula, the location will get changed for sure. As In your case, you have moved the A1 to C1 which is reference of formula in D1 so the reference has been changed.

You can consider the solution given by @Arkadi as in case of using =INDIRECT("A1") in D1 will never change your reference value from A1 to anything even if you do cut paste your A1 cell to another location.

Hope this solution will give you more clarity on your issue.

**haimd** · 04-23-2015, 02:35 PM

Hi Nisha,

Yes, the Arkadi's solution solved my problem.

Thanks,

Haim

**Arkadi** · 04-23-2015, 02:40 PM

Thanks hamid

for the rep also

**Nisha Dhawan** · 04-23-2015, 11:56 PM

Thanks Hamid for adding rep.

**FDibbins** · 04-24-2015, 12:11 AM

My apologies, I misunderstood the question

I thought you wanted to copy/paste the cell with the formula in in, not the referenced cell

Absolute Reference

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Absolute Reference

Re: Absolute Reference

Re: Absolute Reference

Re: Absolute Reference

Re: Absolute Reference

Re: Absolute Reference

Re: Absolute Reference

Re: Absolute Reference

Re: Absolute Reference

Thread Information

Users Browsing this Thread

Similar Threads

dragging formula across a row with absolute column reference but relative row reference

Changing an Absolute Reference to a Relative Reference or Vice Versa in Excel 2007

absolute cell reference in R1C1 reference style

Making VLookup Absolute reference and deleting worksheet reference question

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions