Steved wrote...
I would like a brief explanation to the below formula on what it
is suppose to do. I was given this by this forum. My
understanding is that it will read a row based on criteria from
the Master Sheet, what I would like is how does this do this as
at the moment I am having difficulty to get it to work, Just
outline for me how it works and I will workout the rest.
Pick it apart. First, reformat
=IF(
___ISNUMBER(
_____SMALL(
_______IF(
_________$B$1:$B$6="",
_________"",
_________ROW($B$1:$B$6)
_______),
_______ROW(1:1)
_____)
___),
___INDIRECT(
_____"Master!"
_____&CHAR(COLUMN()+64)
_____&SMALL(
_______IF(
_________$B$1:$B$6="",
_________"",
_________ROW($B$1:$B$6)
_______),
_______ROW(1:1)
_____)
___),
___""
_)
The ISNUMBER(...) test is way too much. If you mean to check whethe
cells in B1:B6 are blank, it's equivalent to the much simpler
COUNTIF($B$1:$B$6,"<>")>=ROW(1:1)
which is an idiom commonly used when you'd fill the formula down int
subsequent rows, so the ROW(...) term would generate an increasin
sequence of integers. This tests whether there's a nonblank cel
corresponding to the ROW(...) result. If only B2 and B4:B5 wer
nonblank, the formula with the ROW(1:1) term would correspond to cel
B2, the formula with the ROW(2:2) term would correspond to cell B4, an
the formula with the ROW(3:3) term would correspond to cell B5. Th
formulas with ROW(4:4) through ROW(6:6) would return "".
The second argument to the outer IF is a dynamic cell reference. It'
be simpler using R1C1 addressing.
INDIRECT("Master!R"
&SMALL(IF($B$1:$B$6<>"",ROW($B$1:$B$6)),ROW(1:1))&"C",0)
This refers to the cell in the same column in the Master worksheet a
the column in which this formula is entered in the row corresponding t
the first nonblank row in B1:B6. When copied down, the next formul
refers to the second nonblank cell in B1:B6, and so on.
So you could rewrite this as
=IF(COUNTIF($B$1:$B$6,"<>")>=ROW(1:1),INDIRECT("Master!R"
&SMALL(IF($B$1:$B$6<>"",ROW($B$1:$B$6)),ROW(1:1))&"C",0),
"")
If you were entering these formulas in col C, and Master!C1:C
contained
a
b
c
d
e
f
and only B2, B4:B5 were nonblank, these formulas would return
b
d
e
<"">
<"">
<"">
where <""> represents a zero length string, which would appear blank