help needed from Excel experts

By the way, I tried (=$a$1) but that didn't work

it should work, when you copy/cut the foruma "=$A$1" and put it in a different cell what does the forumla say?

and are we talking about cutting and pasting across different Tabs in Excel?

Instead of cut and paste, if you copy and paste, then delete A1, the formula in A3 stays the same

paste special, formulas

try indirect(a1)

I must admit I've only ever used indirect once before though!

modk,

when I cut and paste the new formula reads the new paste location. I am actually not cutting. I just wrote that to simplify the problem. Things get messed up when I use the sort function and the cell contents get moved around.

Same happens when you drag cell to a new location, formulas related to that cell go awol.
Cant see any problem with copy and paste or the other suggestions

Starfish, that would work if I were simply cutting and pasting, but I am using the sort function which is essentially the same as cut and paste. Lori, I tried the indirect function but I don't think it works in this case.

Sorry mr. on further reflection indirect is a bit different to that, it keeps the same a1, even if you insert extra lines which is different :(

When you're sorting, do you have any gaps? For instance columns a,b,c,d then a space to make it better viewing, then f?

I find that sometimes really buggers things up

In those cases I always get the specific value in the cell by using C&P then value before doing the sort. If you want to retain the original formula you could introduce an extra column for that purpose. Dont know if that is practical for you

Thanks for you help. No, I don't have any gaps, Lori. I have one worksheet which I want to be automatically updated with the values from another worksheet. However, the data in this other worksheet is continuously moved around by sorting.

I stil dont understand what you mean.

If the following is in cell A3

=$A$1

Then it will display whatever is in A1

If you copy/cut and paste that into and different cell, say A10 what does the foruma say and what is displayed in the cell?

You can give a cell a named reference that can then be accessed anywhere within that workbook , so no need for sheet1A1 type of stuff

Go to the cell you want to keep track of and you'll see at the top of the screen a little white box just above A1 shows the cells address. Click on that reference and ttype in whatever name you want to reference that celll as i'e' call it STAKE. Whenever you want to access that cells value you can just enter =STAKE in any other cell within the whole of the workbook

MR. 69 looks like $A$1 isn't working for you as you're value is on a separate sheet that isn't referenced, if you want to use that method you need to reference the sheet also

Sheet1!$A$1

named references are much easier to keep track of =B2*STAKE

modk. if the value is pasted to cell A10, then the cell will read =$A$10. Joe, I'm not sure how practical this method is since I have data in several thousand cells in this worksheet.

Np Mr.69 not quite sure what the problem is with using $A$1

Lets say I put the following formula in A3 since I want A3 to read whatever is in A1

(=A1)

If then A1 is cut and pasted elsewhere the formula in A3 no longer reads (=A1)

Is there anyway to make A3 always read whatever is in A1 regardless if there is any cutting and pasting going on.

Thanks,

If you only want to track what is in A1, regardless of the original content being pasted elsewhere then either giving A1 a named reference or accessing A1 via a static reference (Sheet1!$a$1) will always show the value of what's in A1 regardless of the original content being moved elsewhere. Having thousands of cells in the workbook won't affect those.

Are you actually trying to keep things relative after copying and pasting ? i.e. you're tracking A3 but when sorted it moves to A1 ?

or just tracking the new value of A1 after everything has been pasted ?

TRY this formula in cell A3
=OFFSET(A3,-2,0,1,1)

Yes Joe. I want the cell to show the new value in A1 when everything has been sorted.

Compound Magic. I don't understand how that works but it does. So what would the formula be in Sheet 2 A10 if I want it to read whatever is in Sheet 1 A10?

=Sheet1!A10

The offset is telling cell A3 to read 2 cells above which is the -2
The 0 after the comma tells A3 that it is in the same column
the next 1 tells it to put the answer in the same row as A3
and the next 1 tells it to put the answer in the same column as A3

If you are having the same problem as before with
=Sheet1!A10

Try
=OFFSET(Sheet1!A1,9,0,1,1)

Thanks Compound. that worked perfectly until I wanted to delete cells after I had sorted them. After deleting I started getting the REF# result in the cells. Is there anyway around this, so that I can both sort and delete?

Instead of deleting a cell or row use clear. That leaves the cells where they are
if you delete a cell it moves other cells with the references no longer valid.

You can still sort. Did you try ~
=OFFSET(Sheet1!A1,9,0,1,1)

If you have a blank row after sorting and clearing that row between rows of data just
do another sort to put the blank row to the bottom.

Are you using excel 2007?

EXCEL 2003

Unfortunately I am running a macro that helps me delete all rows where the 1st cell has a 0 value. I don't think it is possible to do this by clearing instead of deleting.

Excel is great as you say you can do just about anything with it
I don't have excel 2003 so I am not sure where everything is located.

Without seeing what you are sorting and on which sheets it is hard to give
answers. Most of what you do would probably be more easily manipulated with
VBA (Macro's)

I will be online only for another 10 minutes or so. If you still have problems will try to help tomorrow.

Rows ("19:19") . ClearContents
Instead of Rows ("19:19") . Delete Shift:=xlUp

for spreadsheet integrity, you should never really be deleting rows/columns in excel.

You probably need to get the macro to delete the cel(s) rather than entire row.