I'm trying to create a spreadsheet in Excel that will allow me to create 'addressed' formulas that will follow a piece of data around even if I sort it differently. I haven't been able to figure out how to make that work. Any suggestions?
Here's a general description:
I have a table of data that could be sorted by many different parts of the data.
I want to be able to sort differently, or change the data and resort by the same criterion.
I need to use formulas for sums, etc.
I would like to not have to rewrite the formulas every time I resort if the resorting changes the sequence of the data (which it is likely to do).
I've tried:
Naming cells. The default is to have fixed cell references. They won't change to accommodate sorting. I've tried using variable cell references (getting rid of the $'s). That didn't work either.
Using SUMIF. I can't figure out how to assign a list of relative "if's". For instance, I want it to add values up if the cell to the right of the value is one of a list of names.
I can do this in access but my boss wants in done in excel. Please help me out. Thanks.
Here's a general description:
I have a table of data that could be sorted by many different parts of the data.
I want to be able to sort differently, or change the data and resort by the same criterion.
I need to use formulas for sums, etc.
I would like to not have to rewrite the formulas every time I resort if the resorting changes the sequence of the data (which it is likely to do).
I've tried:
Naming cells. The default is to have fixed cell references. They won't change to accommodate sorting. I've tried using variable cell references (getting rid of the $'s). That didn't work either.
Using SUMIF. I can't figure out how to assign a list of relative "if's". For instance, I want it to add values up if the cell to the right of the value is one of a list of names.
I can do this in access but my boss wants in done in excel. Please help me out. Thanks.