Comments on: How to Sort Linked Sheets in Excel Without Screwing up the Linked Data

on 6/5/2008 Hi, I can not agree with the statement above:
"If you cut cell B2 and paste it three cells over to the right, the formula will move the referenced location three cells over to the right also." – this is a complete nonsense. If you cut and paste the cell, the contained formula won't change. And it is not dependent on the reference type. This also does not explain why the sorting changes the contents of the sorted cells. Only if you copy the formula then Excel updates the cell content if it is not an absolute ($$) reference.
I had a problem with the sorting too. I have solved it using the INDIRECT function. This happend to be a very useful formula. The main idea behind is - a user can define absolute reference to other cell's row/column/both in a separate cell. I will explain its functioning on an example. Let’s say we have a scrap of spreadsheet (I will call it a ‘list’) containing two

on 2/28/2008 Retta19 - this article's focus is on keeping a formula from changing when the cell containing the formula is relocated due to sorting, not cutting/copying/pasting. Excel always updates formulas to reflect new locations when you move data around by cutting/pasting. Maybe you could use a reference formula like LOOKUP or INDEX to find data regardless of specific cell location.

on 2/27/2008 this doesn't work for me. if i cut cell D33 and paste it into D25, the formula in my 2nd sheet changes from ='FT wk1'!$D$33 to ='FT wk1'!$D$25. this doesn't make any sense and it's driving me crazy!!! i don't want to have to cut/paste/delete. any suggestions?? (I am using the 2003 version)

on 5/31/2007 Congrats on having this article be picked as the winner for the "Top Written Requested How to Article!" Check out the forums and see which other winners we have this week. Check it out at:

http://www.ehow.com/community/forums/topic_15235_top-most-requested-articles:-weekly-submission-winners.aspx

-Rich

on 5/28/2007 Wow! This is an excellent tip! I, too, use Excel all the time, and I am very glad to see this helpful article. Thank you very much!

on 5/22/2007 Hey Melody, this article is spot on! I too had a brief moment with a finance profession and this is exactly what we had to deal with.