Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference to cells using autofilter problem
Hello,
my problem seems pretty simple. Let's say I have a sheet with 3 columns: first name, last name and age. I enable the "autofilter" function to decide if I want to sort by first name or last name. Somewhere on another sheet I make a reference to someone's age. Let's say that I point to Bill on row 3 so his age is in cell C3. If on another sheet I make a reference to C3 (for bill's age) but then I use the autofilters to change the sort... the other sheet still refers to C3 instead of being updated with Bill's new cell for his age. I just realised that when I use the SORT function, it still does the same thing... the reference doesn't get updated if I resort.. How do I do this?? Am I missing something? I of course want to have the cross-sheet references updated when resorting! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference to cells using autofilter problem
Autofilter does not actually sort the data, it is just showing you sections
of the data you are asking to see. You should get the results you want if you actually do a data..sort however unless you are pointing to an absolute cell address in your second sheet. What does your formula look like in the 2nd sheet? ={sheetname}C3 or ={sheetname}$C$3? (sheetname would be the actual name of the sheet your guys age was in.) "Twitos" wrote: Hello, my problem seems pretty simple. Let's say I have a sheet with 3 columns: first name, last name and age. I enable the "autofilter" function to decide if I want to sort by first name or last name. Somewhere on another sheet I make a reference to someone's age. Let's say that I point to Bill on row 3 so his age is in cell C3. If on another sheet I make a reference to C3 (for bill's age) but then I use the autofilters to change the sort... the other sheet still refers to C3 instead of being updated with Bill's new cell for his age. I just realised that when I use the SORT function, it still does the same thing... the reference doesn't get updated if I resort.. How do I do this?? Am I missing something? I of course want to have the cross-sheet references updated when resorting! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference to cells using autofilter problem
Well, I did a few tests and the problem is much simpler. I have the same
problem even if I don't do cross-sheet and even if I use data -- sort. Nothing related to autofilters then. If I have a cell that makes a reference to C3 (obviously NOT $C$3) and then so data-- sort... my cell is still pointing to the new data in C3... :| "tim m" wrote: Autofilter does not actually sort the data, it is just showing you sections of the data you are asking to see. You should get the results you want if you actually do a data..sort however unless you are pointing to an absolute cell address in your second sheet. What does your formula look like in the 2nd sheet? ={sheetname}C3 or ={sheetname}$C$3? (sheetname would be the actual name of the sheet your guys age was in.) "Twitos" wrote: Hello, my problem seems pretty simple. Let's say I have a sheet with 3 columns: first name, last name and age. I enable the "autofilter" function to decide if I want to sort by first name or last name. Somewhere on another sheet I make a reference to someone's age. Let's say that I point to Bill on row 3 so his age is in cell C3. If on another sheet I make a reference to C3 (for bill's age) but then I use the autofilters to change the sort... the other sheet still refers to C3 instead of being updated with Bill's new cell for his age. I just realised that when I use the SORT function, it still does the same thing... the reference doesn't get updated if I resort.. How do I do this?? Am I missing something? I of course want to have the cross-sheet references updated when resorting! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Groups of cells between workbooks | Excel Discussion (Misc queries) | |||
Array, circular reference problem | Excel Worksheet Functions | |||
COUNT NON-BLANK CELLS WITH REFERENCE TO ANOTHER COLUMN | Excel Worksheet Functions | |||
Expanding conditional formating with reference cells changing | Excel Discussion (Misc queries) | |||
Help me! There is problem with cells view... | Excel Discussion (Misc queries) |