Find most recent value in data range that includes SUM formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RPage@SF - 12 May 2008 20:36 GMT

I have a series of cells that sum the columns above them (O12:X12) on
multiple worksheets. I would like to write a formula to report the cell that
has a value and is the furthest to the right. I used:

=LOOKUP(10^10,'Worksheet reference'!O12:X12)

This worked on a previous worksheet when the data range contained values,
but since O12 through X12 include SUM formulas, it's not working.

Any ideas?

Reply to this Message

Gary''s Student - 12 May 2008 21:58 GMT

We can force your LOOKUP() to work!!

In O12 you may have something like:
=SUM(O1:O11)
replace it with:
=IF(SUM(O1:O11)=0,"",SUM(O1:O11)) and copy across

This way if the SUM() reports a zero, we make it a blank and your LOOKUP()
will be happy again.

Signature

Gary''s Student - gsnu200785

> I have a series of cells that sum the columns above them (O12:X12) on
> multiple worksheets. I would like to write a formula to report the cell that
[quoted text clipped - 6 lines]
>
> Any ideas?

Reply to this Message

RPage@SF - 12 May 2008 22:31 GMT

Now it reports "#NA" if the value is zero, but correctly if there are values
to be summed. How can I get it to report 0 if there isn't a value to report?

> We can force your LOOKUP() to work!!
>
[quoted text clipped - 16 lines]
> >
> > Any ideas?

Reply to this Message

T. Valko - 13 May 2008 18:01 GMT

Try this:

=IF(COUNT('Worksheet reference'!O12:X12),LOOKUP(1E100,'Worksheet
reference'!O12:X12),"")

Signature

Biff
Microsoft Excel MVP

> Now it reports "#NA" if the value is zero, but correctly if there are
> values
[quoted text clipped - 24 lines]
>> >
>> > Any ideas?

Reply to this Message

T. Valko - 12 May 2008 21:59 GMT

It should work. It doesn't make a difference whether the values are
constants or the results of formulas. As long as the values are *numeric
numbers* the formula should work.

Signature

Biff
Microsoft Excel MVP

>I have a series of cells that sum the columns above them (O12:X12) on
> multiple worksheets. I would like to write a formula to report the cell
[quoted text clipped - 7 lines]
>
> Any ideas?

Reply to this Message

RPage@SF - 13 May 2008 15:04 GMT

What does the "10^10" part of the equation mean?

> It should work. It doesn't make a difference whether the values are
> constants or the results of formulas. As long as the values are *numeric
[quoted text clipped - 11 lines]
> >
> > Any ideas?

Reply to this Message

T. Valko - 13 May 2008 18:05 GMT

10 to the 10th power or 10,000,000,000

Signature

Biff
Microsoft Excel MVP

> What does the "10^10" part of the equation mean?
>
[quoted text clipped - 14 lines]
>> >
>> > Any ideas?

Reply to this Message