Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find most recent value in data range that includes SUM formulas
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find most recent value in data range that includes SUM formulas
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. -- Gary''s Student - gsnu200785 "RPage@SF" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find most recent value in data range that includes SUM formulas
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. -- Biff Microsoft Excel MVP "RPage@SF" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find most recent value in data range that includes SUM formula
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? "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200785 "RPage@SF" wrote: 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find most recent value in data range that includes SUM formula
What does the "10^10" part of the equation mean?
"T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "RPage@SF" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find most recent value in data range that includes SUM formula
Try this:
=IF(COUNT('Worksheet reference'!O12:X12),LOOKUP(1E100,'Worksheet reference'!O12:X12),"") -- Biff Microsoft Excel MVP "RPage@SF" wrote in message ... 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? "Gary''s Student" wrote: 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. -- Gary''s Student - gsnu200785 "RPage@SF" wrote: 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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find most recent value in data range that includes SUM formula
10 to the 10th power or 10,000,000,000
-- Biff Microsoft Excel MVP "RPage@SF" wrote in message ... What does the "10^10" part of the equation mean? "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "RPage@SF" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find the most recent date | Excel Discussion (Misc queries) | |||
what is the most recent version of Office (includes Excel)? | Excel Discussion (Misc queries) | |||
How to find all formulas that used a certain named range | Excel Discussion (Misc queries) | |||
=max(range includes #N/As) | Excel Worksheet Functions | |||
How do I sum a range which includes the "#N/A" VLOOKUP return valu | Excel Worksheet Functions |