Moving data without altering the formula which references its cell

I am using excel to help me generate some SQL queries. I have a cell which generates the queries, then another 3 cells which stores a user id, first name, and last name. I also have cells which store a date and time, which is unique for each user. Basically, what I need to do is move around the user data (id and name) depending on their ranking. Now, when I copy one user, and swap them with another user, the formulas still reference the original data. Example, the formula in C1 should always reference the user id in C2, but if I switch C2 and D2 by copy/pasting, C1 will reference D2, and D1 will reference C2. The only solution I have found so far is to do all my moving around, then re-drag the formula to overwrite everything. Is there a way to make C1 always reference C2, no matter what, even if I copy/paste the data elsewhere?

Thanks!

Joe

I didn't fully understand the details of your request, but generally speaking, if you want a formula within a cell to always refer to that cell, try using the indirect function.

=INDIRECT("C1") Will always refer to cell C1, no matter where it is copied and pasted to.

Try this, I believe it's called absolute reference (but I could be wrong):
If the cell you want to stay put is C1, in the formula use $C$1. I'm not sure from your post if SQL is directly referencing these cells. If it is, I'm pretty sure it won't work. Otherwise, this should work. Should you want the 1 to move around but not the C, then you would use $C1. Basically the dollar sign will ensure that the reference immediately after stays as is, regardless of what happens to the formula (cut/, copy, paste)

Originally Posted by BigBas

I didn't fully understand the details of your request, but generally speaking, if you want a formula within a cell to always refer to that cell, try using the indirect function.

=INDIRECT("C1") Will always refer to cell C1, no matter where it is copied and pasted to.

BigBas, that is almost what I need. Let me try and re-explain my problem. If a cell, lets say A1, has the following equation:

=A2

And then A2 has some random number, lets say 12345. If I copy and paste the cell A2 to B5, the equation in A1 automatically changes to the following:

=B5

I don't want it to do this, I want it to always reference A2, no matter what. The function to gave me does that, but when I 'drag' the cell to copy and the formula down for another 100 cells, the cell in the formula stays the same. I need that to change.

I am never copying and pasting the cells containing a formula, I am moving around the cells that the formula references. When I do this, I don't want the formula to reference where the data has moved to, but instead, keep referencing the same cell as before.

I think I am confused. Using your example, cell A1 contains a formula, =A2. And A2 contains a random number (12345). If you copy and paste cell A2, it will not have any effect on cell A1. (set up a test file and try it out).

Judging from your description, you are either using the CUT method, or actually clicking on cell A2, and dragging it to a new cell. I'd say the best way to avoid the cell changing references is to copy the cell, paste it into the new location, then clear out the old cell. I know it is a nuisance, but I don't know of a workaround (maybe someone else does)>

BigBas, you are correct. I am cutting the data, not copying. My fault for using the term copy/paste. I should have been more specific. What I meant was, if you cut cell A2 and paste to B5, A1 will now reference B5 instead of A2, and I was trying to find a way to prevent that from happening

I actually think I found a solution. I can do something like this, which will also allow me to copy and paste the formula to multiple rows, but when I cut data the cell references, the formula wont change. Example:

=INDIRECT("C" & ROW() & "");

When this is pasted into Column A, for example, and I drag it for 100 rows, it will always reference the C column in that row, even if I cut/paste data in the C column and move it elsewhere.

Thanks for letting me know about the INDIRECT() function though, that helped a bunch!