Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
When I sort how do I keep cell references correct?
When I sort a table references to other cells are not maintained properly.
I've tried making them both relative & absolute & either way they no longer point to the correct cell (which has moved) after the sort is completed. These are all internal references (within the same table) but on a different row. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
When I sort how do I keep cell references correct?
Select everything below your reference cells and be sure you select all the columns including the column with with your formula - otherwise the columns will no longer be in synch. -- wjohnson ------------------------------------------------------------------------ wjohnson's Profile: http://www.hightechtalks.com/m390 View this thread: http://www.hightechtalks.com/t2300012 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
When I sort how do I keep cell references correct?
Thanks but that doesn't help. I'm selecting all rows in the worksheet.
The cell references to an above or below cell are not maintained. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
When I sort how do I keep cell references correct?
GLS, I've stumbled through this before, without a good solution. Clumsy solutions which I've used and which you may try are to either (a) re-write the formulas to use vlookup or sumif features, or (b) use helper calculations on another worksheet. When you sort the main worksheet, the references to the helper sheet remain correct. -- rsenn ------------------------------------------------------------------------ rsenn's Profile: http://www.excelforum.com/member.php...o&userid=29050 View this thread: http://www.excelforum.com/showthread...hreadid=490197 |
#5
|
|||
|
|||
Quote:
What you do is you have two separate sheets (or even files) -- one for data entry and one for data sorts. So sheet 1 contains your data and formulae. You then on sheet 2 have the whole sheet pointing to the equivalent cell in sheet 1. You then enter data only in sheet 1 and when you want to sort it you only do it on sheet 2. Yippee. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel insits that I use absolute cell references | Excel Discussion (Misc queries) | |||
Cell References | Excel Discussion (Misc queries) | |||
Formulas within Cell References | Excel Discussion (Misc queries) | |||
Sumif function with remote cell references | Excel Worksheet Functions | |||
Syntax for inferred cell references | Excel Worksheet Functions |