sorting formulas

M

m4tt

Hi,

Ive made a spreadsheet which all the cells accept titles contain look
up formula's. I want to be able to do sorts on the columns but it
doesnt seem to work. I think its because excel recognises the content
of the cells as a formula, but i want it just to see the result of the
look up and sort that.

Is there anyway to solve this problem?


Thanks to all who help.

Matt
 
E

Earl Kiosterud

m4tt,

I'm not sure what you need. Are the cells containing a VLOOKUP in the table
being sorted? If so, they get effectively copied (that's how sorting
"moves" cells) to their new row when the table is sorted. It may be that
your LOOKUP functions need absolute references to the table they're looking
in. Or you may need to convert them to data (copy, then paste-special right
over them), in which case the title cannot change for that row, since the
VLOOKUP isn't there any more. I think an example of a couple of the rows of
your table would be useful. And some of the table in which the VLOOKUPs are
looking.
 
D

David McRitchie

Hi Matt,

Without examples can't tell what your problem is, but if you have formulas
referring to other rows on the same worksheet you could have a problem.

For instance you would not want to use a formula like
E57: =C57+E56
instead you would want to use
E57: =C57+OFFSET(E57,-1,0)

applies to inserting / deleting rows as well as sorting rows.
 
Top