I have a "source data" worksheet and a "summary" sheet. The summary
sheet contains If Formulas referencing the source data sheet. Ex:
=IF(Sheet1!A1="","",Sheet1!A1). The problem is that when I sort the
data on the summary sheet, it sorts by the formula contained within the
cell. Is there a way to force it to sort based on value instead?

Excel always sorts on values, not on formulas.

The only way Excel will sort on a "formula" is if it's stored as text in the
cell. You can tell what it sorts on by what it displays in the cell. Is it
displaying the formula or the result? If it is displaying the formula, you need
to change the format to General, F2 the formula, then F9 (recalc) it. When you
get the result displayed, that's what Excel will sort on.

By the way, your formula can be simplified to =Sheet1!A1

--
Regards,
Fred


"AntnyMI" wrote in message
oups.com...
I have a "source data" worksheet and a "summary" sheet. The summary
sheet contains If Formulas referencing the source data sheet. Ex:
=IF(Sheet1!A1="","",Sheet1!A1). The problem is that when I sort the
data on the summary sheet, it sorts by the formula contained within the
cell. Is there a way to force it to sort based on value instead?

Fred,
Addressing your points in reverse order, "=Sheet1!A1" is how I
originally wrote the formula. However, when sorting in descending
order, this would return all of the blank/empty rows first. I thought
adding the extra IF statement would correct this, but it didn't.

Any idea what I'm doing wrong?


Fred Smith wrote:
Excel always sorts on values, not on formulas.

The only way Excel will sort on a "formula" is if it's stored as text in the
cell. You can tell what it sorts on by what it displays in the cell. Is it
displaying the formula or the result? If it is displaying the formula, you need
to change the format to General, F2 the formula, then F9 (recalc) it. When you
get the result displayed, that's what Excel will sort on.

By the way, your formula can be simplified to =Sheet1!A1

--
Regards,
Fred


"AntnyMI" wrote in message
oups.com...
I have a "source data" worksheet and a "summary" sheet. The summary
sheet contains If Formulas referencing the source data sheet. Ex:
=IF(Sheet1!A1="","",Sheet1!A1). The problem is that when I sort the
data on the summary sheet, it sorts by the formula contained within the
cell. Is there a way to force it to sort based on value instead?

You're not doing anything wrong--but excel doesn't work the way you want it to
work.

When I have to do stuff like this, I'll try to find a column/field that contains
a unique code per row. Then I'll put those values (not formulas) in my summary
worksheet.

Then I can use =vlookup() or =index(match()) to return the values other fields
on that row with the unique key.

AntnyMI wrote:

Fred,
Addressing your points in reverse order, "=Sheet1!A1" is how I
originally wrote the formula. However, when sorting in descending
order, this would return all of the blank/empty rows first. I thought
adding the extra IF statement would correct this, but it didn't.

Any idea what I'm doing wrong?

Fred Smith wrote:
Excel always sorts on values, not on formulas.

The only way Excel will sort on a "formula" is if it's stored as text in the
cell. You can tell what it sorts on by what it displays in the cell. Is it
displaying the formula or the result? If it is displaying the formula, you need
to change the format to General, F2 the formula, then F9 (recalc) it. When you
get the result displayed, that's what Excel will sort on.

By the way, your formula can be simplified to =Sheet1!A1

--
Regards,
Fred


"AntnyMI" wrote in message
oups.com...
I have a "source data" worksheet and a "summary" sheet. The summary
sheet contains If Formulas referencing the source data sheet. Ex:
=IF(Sheet1!A1="","",Sheet1!A1). The problem is that when I sort the
data on the summary sheet, it sorts by the formula contained within the
cell. Is there a way to force it to sort based on value instead?


--

Dave Peterson

I think you're right. There might not be a way to do this in Excel.
However, in the event I didn't explain everything correctly, I included
more of the background info in the following post: "Pulling Data from
Another Worksheet"

Dave Peterson wrote:
You're not doing anything wrong--but excel doesn't work the way you want it to
work.

I think you explained everything ok.

But you may want to reconsider and keep all your data in one location. Between
sorting and autofiltering, you may find that you're able to do more things with
it.

AntnyMI wrote:

I think you're right. There might not be a way to do this in Excel.
However, in the event I didn't explain everything correctly, I included
more of the background info in the following post: "Pulling Data from
Another Worksheet"

Dave Peterson wrote:
You're not doing anything wrong--but excel doesn't work the way you want it to
work.

--

Dave Peterson