Sequential series 'check'

W

wuddus

I need a formula that will return the first available
value in a series of cells that I specify. For example,
in the series:

'Sheet1'!G5
'Sheet2'!G5
'Sheet3'!G5
'Sheet4'!G5

How can I spcify that the value in 'Summary Sheet'!G5
should equal the value in 'Sheet4'!G5 UNLESS that latter
cell is blank, in which case the value of 'Summary Sheet'!
G5 should equal the value of 'Sheet3'!G5, UNLESS that
latter cell is blank, and so on? If

'Sheet1'!G5= 89
'Sheet2'!G5= 90
'Sheet3'!G5= 106
'Sheet4'!G5= [blank]

then 'Summary Sheet'!G5 should equal 106. Essentially,
the cell in 'Summary Sheet' should always check, IN
SEQUENCE, its counterpart G5 cells in the other sheets and
always return the first value it comes to.

If anyone can help with this, a low-level corporate drone
will be extremely appreciative. Thank you in advance!
 
J

JulieD

hi

not sure if there is a better way, but this is one way
=if(sheet4!g5="",if(sheet3!g5="",if(sheet2!g5="",sheet1!g5,sheet2!g5),sheet3
!g5),sheet4!g5)

Cheers
Julie
 
H

Harlan Grove

Aladin Akyurek said:
Also...

=LOOKUP(9.99999999999999E+307,THREED(Sheet1:Sheet4!$G$5))
Click to expand...
....

Caveat: this only works if the values in Sheet1:Sheet4!G5 are numbers. If
they're all text, this formula returns #N/A. If Sheet4!G5 were text while
Sheet3!G5 were numeric, the formula above would return the value in
Sheet3!G5 despite Sheet4!G5 *NOT* being blank.

The reliable way to do this for numbers or text requires putting the
worksheet names in an array of strings or a range, which I'll refer to as
WSLst, and using an array formula like

=INDIRECT("'"&INDEX(WSLst,MATCH(0,COUNTIF(INDIRECT("'"&WSLst&
"'!"&CELL("Address",G5)),"<>"),0)-1)&"'!"&CELL("Address",G5))

which doesn't need an add-in and does return text when appropriate.
 
A

Aladin Akyurek

Harlan Grove said:
...

Caveat: this only works if the values in Sheet1:Sheet4!G5 are numbers. If
they're all text, this formula returns #N/A. If Sheet4!G5 were text while
Sheet3!G5 were numeric, the formula above would return the value in
Sheet3!G5 despite Sheet4!G5 *NOT* being blank.
Click to expand...

It's not a caveat, rather the intent. Disregarding the need for the add-in
for a moment... If the OP's objective is to determine *the last numerical
value in the target 3d reference*, the formula will work as advertised. (The
OP's sample consists of numeric data along with the accompanying description
made me to "jump to the conclusion" that he/she is interested in the last
numeric value.

If the intent is to fetch the last text value...

=LOOKUP(REPT("z",255),THREED(Sheet1:Sheet3!G5))

would be the formula to use.
[The reliable way to do this for numbers or text requires...]
Click to expand...

If the objective is to get the last value, either a text (including a
formula blank) or a numerical value or any value irrespective its type, the
LOOKUP formula above obviously won't apply.
The reliable way to do this for numbers or text requiresputting the
worksheet names in an array of strings or a range, which I'll refer to as
WSLst, and using an array formula like

=INDIRECT("'"&INDEX(WSLst,MATCH(0,COUNTIF(INDIRECT("'"&WSLst&
"'!"&CELL("Address",G5)),"<>"),0)-1)&"'!"&CELL("Address",G5))

which doesn't need an add-in and does return text when appropriate.
Click to expand...


If the objective is to get the last numerical value and the use of the
suggested add-in is out of question, then:

=LOOKUP(9.99999999999999E+307,IF(SUBTOTAL(2,INDIRECT("'"&WSLst&"'!"&CELL("Ad
dress",G5)))>0,1,""),SUBTOTAL(9,INDIRECT("'"&WSLst&"'!"&CELL("Address",G5)))
)

which must be confirmed with control+shift+enter instead of just enter.

SUBTOTAL() is used throughout to overcome the dereferencing problem.

Judging from my not so extensive search, there is no formula, similar in
form to the latter LOOKUP formula if the objective is to capture *the last
text value in a 3d reference* and the use of the morefunc add-in is out of
question.
 
H

Harlan Grove

Aladin Akyurek said:
....
It's not a caveat, rather the intent. Disregarding the need for the
add-in for a moment... If the OP's objective is to determine *the last
numerical value in the target 3d reference*, the formula will work as
advertised. (The OP's sample consists of numeric data along with the
accompanying description made me to "jump to the conclusion" that
he/she is interested in the last numeric value.
Click to expand...
....

I don't doubt that the OP may only need this for numbers, in which case your
formula works. I added the caveat because sometimes respondents refer OPs to
the archives, and my caveat was meant for the archives. Glad you agree that
LOOKUP isn't a panacea.
If the objective is to get the last numerical value and the use of the
suggested add-in is out of question, then:

=LOOKUP(9.99999999999999E+307,
IF(SUBTOTAL(2,INDIRECT("'"&WSLst&"'!"&CELL("Address",G5)))>0,1,""),
SUBTOTAL(9,INDIRECT("'"&WSLst&"'!"&CELL("Address",G5))))

which must be confirmed with control+shift+enter instead of just enter.

SUBTOTAL() is used throughout to overcome the dereferencing problem.
Click to expand...

The '>>' formula uses 7 function calls in 5 nested levels, and FWLIW doesn't
need to be an array formula. The '>' formula uses 8 function calls in 4
nested levels, and must be entered as an array formula. Also, the '>>'
formula returns the 'last' value whether it's numeric, text, boolean or
error. The '>' formula returns only numbers. 7/5 vs 8/4 is a design
trade-off, as are, I suppose, the other points.
Judging from my not so extensive search, there is no formula, similar
in form to the latter LOOKUP formula if the objective is to capture
*the last text value in a 3d reference* and the use of the morefunc
add-in is out of question.
Click to expand...

If the intent is to locate the 'last' text or number value in a 3D reference
that actually degenerates to a 1D array across worksheets when that 1D
reference could contain both text and numbers, then it could be accomplished
with

text:
=INDIRECT("'"&INDEX(WSLst,MAX(COUNTIF(INDIRECT("'"&WSLst&"'!"
&CELL("Address",G5)),"*")*ROW(INDIRECT("1:"&COUNTA(WSLst)))))
&"'!"&CELL("Address",G5))

number:
=INDIRECT("'"&INDEX(WSLst,MAX(COUNTIF(INDIRECT("'"&WSLst&"'!"
&CELL("Address",A1)),"<=9.99999999999999E307")*ROW(INDIRECT("1:"
&COUNTA(WSLst)))))&"'!"&CELL("Address",A1))

However, either of these could also be done using LOOKUP.

text:
=LOOKUP(RealLong,IF(COUNTIF(INDIRECT("'"&WSLst&"'!"
&CELL("Address",G5)),"*"),T(INDIRECT("'"&WSLst&"'!"
&CELL("Address",G5)))))

number:
=LOOKUP(RealBig,IF(COUNTIF(INDIRECT("'"&WSLst&"'!"
&CELL("Address",G5)),"<="&RealBig),N(INDIRECT("'"&WSLst&"'!"
&CELL("Address",G5)))))

where RealLong is defined as =REPT("z",255") and RealBig is defined as
=9.9999999999999E307.

Whatever your preferences in deciding which of these to use, there's one
objective observation I'll make: SUBTOTAL is the wrong function to use to
solve the 'dereferencing problem'. If you expect text, use T(). If you
expect numbers, use N(). If you'll accept either, use the '>>' formula.
 
H

Harlan Grove

...
...
The '>>' formula uses 7 function calls in 5 nested levels, and FWLIW doesn't
need to be an array formula. . . .
Click to expand...
...

It *DOES* have to be entered as an array formula. Dumb!
 
Top