explanation for formula Please.

[Steved]

Hello from Steved

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.

{=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))),"")}

Thankyou.

 

[Biff]

Hi Steved!

That formula does just what you described but it depends
on what type of data is in B1:B6. It uses those values as
a reference to the Master sheet.

What kind of data do you put in B1:B6.

At first glance, to me it looks like the first part of the
formula might be backwards. But without knowing what
values are in B1:B6 I can't test it.

Biff

 

[Steved]

Hello From Steved

I have a Master Sheet in which I have information
from Col A1 to Q6. In col A have the row number,
Col B I have the date, Col C I have the time Col D
I have the customers name Col E I have the Customers
Identification Number, Col G product Number, Col H to Q
are all numeric and Col R Comments Col, Col D and Col R
are the only Text Colums.

Ok in the Master in Col S I have =VALUE(LEFT(G1,1))
I have A 4 numeric Customer number The first numeric of
the number is unique ie 4001 in this case 4.

I have 9 Sheets in this case Col A1 to Q1 has the
customer number 4001 so this been the case I want to
copy from A1 to Q1 an place in sheet 4

In Sheet4 in col A I have this formula =IF(Master!R1=4,
ROW(),"")

{=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 above Formula is in Col F1 to Q6

What I trying to acheive is to copy from Master to in
this case SHEET4

Thankyou


-----Original Message-----
Hi Steved!

That formula does just what you described but it depends
on what type of data is in B1:B6. It uses those values as
a reference to the Master sheet.

What kind of data do you put in B1:B6.

At first glance, to me it looks like the first part of
the formula might be backwards. But without knowing what
values are in B1:B6 I can't test it.

Biff

 

[Steved]

Hello From Steved

I have a Master Sheet in which I have information
from Col A1 to Q6. In col A have the row number,
Col B I have the date, Col C I have the time Col D
I have the customers name Col E I have the Customers
Identification Number, Col G product Number, Col H to Q
are all numeric and Col R Comments Col, Col D and Col R
are the only Text Colums.

Ok in the Master in Col S I have =VALUE(LEFT(G1,1))
I have A 4 numeric Customer number The first numeric of
the number is unique ie 4001 in this case 4.

I have 9 Sheets in this case Col A1 to Q1 has the
customer number 4001 so this been the case I want to
copy from A1 to Q1 an place in sheet 4

In Sheet4 in col A I have this formula =IF(Master!R1=4,
ROW(),"")

{=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 above Formula is in Col F1 to Q6

What I trying to achieve is to copy from Master to in
this case SHEET4

Thankyou


-----Original Message-----
Hi Steved!

That formula does just what you described but it depends
on what type of data is in B1:B6. It uses those values as
a reference to the Master sheet.

What kind of data do you put in B1:B6.

At first glance, to me it looks like the first part of
the formula might be backwards. But without knowing what
values are in B1:B6 I can't test it.

Biff

 

[hgrove]

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

 

[Steved]

Thankyou hgrove

You have just given me the tools to break it down
much appreciated.