sorting formulas

[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

[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.
--
Earl Kiosterud
www.smokeylake.com

"m4tt" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> m4tt
> ------------------------------------------------------------------------
> m4tt's Profile:
> http://www.excelforum.com/member.php...o&userid=25835
> View this thread: http://www.excelforum.com/showthread...hreadid=399142
>

[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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Earl Kiosterud" <[email protected]> wrote in message news:[email protected]...
> 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.
> --
> Earl Kiosterud
> www.smokeylake.com
>
> "m4tt" <[email protected]> wrote in message
> news:[email protected]...
> >
> > 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
> >
> >
> > --
> > m4tt
> > ------------------------------------------------------------------------
> > m4tt's Profile:
> > http://www.excelforum.com/member.php...o&userid=25835
> > View this thread: http://www.excelforum.com/showthread...hreadid=399142
> >
>
>