Eliminating Blank rows
 

I am hoping someone can help me!!!!

I have on one sheet a product selector for my sales team. I have added a
column where they can put an "x" into if they would like to quote that
product to a client. I have created another sheet called "Quote" where the
prodcut details are automatically updated from the "Product Selector".
However if they choose the products listed in ROW 1 and 3 they are copied
the same way to the quote with blank lines in between. How can I make sure
that they are copied over but without the blank lines

A
1 =IF(ProductSelector!R12="X",(ProductSelector!I12), "")
2 =IF(ProductSelector!R13="X",(ProductSelector!I13), "")
3 =IF(ProductSelector!R14="X",(ProductSelector!I14," ")

And so on, the problem is that if they put and "X" in B1 and B3 the formula that is brought over to the other worksheet is also in A1 and A3 and want to know if there is something that I can add to the formula so that if A2 is blank the result from A3 is moved to there.

Eliminating Blank rows
 

In cell A1, array enter (enter using Ctrl-Shift-Enter) the formula

=INDEX(ProductSelector!$I:$I,SMALL(IF(ProductSelec tor!$R$12:$R$100="X",ROW(ProductSelector!$R$12:$R$ 100)),ROWS(B$1:B1)))

and copy down.

You can hide the error values by using this slightly more complicated version:

=IF(COUNTIF(ProductSelector!R:R,"X")=ROWS($B$1:B1 ),INDEX(ProductSelector!$I:$I,
SMALL(IF(ProductSelector!$R$12:$R$100="X",ROW(Prod uctSelector!$R$12:$R$100)),ROWS(B$1:B1))),"")

HTH,
Bernie
MS Excel MVP


"Lversteeg" wrote in message
...
I am hoping someone can help me!!!!

I have on one sheet a product selector for my sales team. I have added a
column where they can put an "x" into if they would like to quote that
product to a client. I have created another sheet called "Quote" where the
prodcut details are automatically updated from the "Product Selector".
However if they choose the products listed in ROW 1 and 3 they are copied
the same way to the quote with blank lines in between. How can I make sure
that they are copied over but without the blank lines

A
1 =IF(ProductSelector!R12="X",(ProductSelector!I12), "")
2 =IF(ProductSelector!R13="X",(ProductSelector!I13), "")
3 =IF(ProductSelector!R14="X",(ProductSelector!I14," ")

And so on, the problem is that if they put and "X" in B1 and B3 the formula that is brought over
to the other worksheet is also in A1 and A3 and want to know if there is something that I can add
to the formula so that if A2 is blank the result from A3 is moved to there.

Eliminating Blank rows
 

Thanks Bernie, I can't get it to calculate correctly though. The Product
Selector has a completely different layout than the Quote and I only want
certain information for there to be taken to the quote. And when I get to the
prices there are too many IF's to incorporate the formula that you sent me.

Thanks anyway

"Bernie Deitrick" wrote:

In cell A1, array enter (enter using Ctrl-Shift-Enter) the formula

=INDEX(ProductSelector!$I:$I,SMALL(IF(ProductSelec tor!$R$12:$R$100="X",ROW(ProductSelector!$R$12:$R$ 100)),ROWS(B$1:B1)))

and copy down.

You can hide the error values by using this slightly more complicated version:

=IF(COUNTIF(ProductSelector!R:R,"X")=ROWS($B$1:B1 ),INDEX(ProductSelector!$I:$I,
SMALL(IF(ProductSelector!$R$12:$R$100="X",ROW(Prod uctSelector!$R$12:$R$100)),ROWS(B$1:B1))),"")

HTH,
Bernie
MS Excel MVP


"Lversteeg" wrote in message
...
I am hoping someone can help me!!!!

I have on one sheet a product selector for my sales team. I have added a
column where they can put an "x" into if they would like to quote that
product to a client. I have created another sheet called "Quote" where the
prodcut details are automatically updated from the "Product Selector".
However if they choose the products listed in ROW 1 and 3 they are copied
the same way to the quote with blank lines in between. How can I make sure
that they are copied over but without the blank lines

A
1 =IF(ProductSelector!R12="X",(ProductSelector!I12), "")
2 =IF(ProductSelector!R13="X",(ProductSelector!I13), "")
3 =IF(ProductSelector!R14="X",(ProductSelector!I14," ")

And so on, the problem is that if they put and "X" in B1 and B3 the formula that is brought over
to the other worksheet is also in A1 and A3 and want to know if there is something that I can add
to the formula so that if A2 is blank the result from A3 is moved to there.

Eliminating Blank rows
 

Post a little bit of your data, and describe what you would want returned.

Bernie


"Lversteeg" wrote in message
...
Thanks Bernie, I can't get it to calculate correctly though. The Product
Selector has a completely different layout than the Quote and I only want
certain information for there to be taken to the quote. And when I get to
the
prices there are too many IF's to incorporate the formula that you sent
me.

Thanks anyway

"Bernie Deitrick" wrote:

In cell A1, array enter (enter using Ctrl-Shift-Enter) the formula

=INDEX(ProductSelector!$I:$I,SMALL(IF(ProductSelec tor!$R$12:$R$100="X",ROW(ProductSelector!$R$12:$R$ 100)),ROWS(B$1:B1)))

and copy down.

You can hide the error values by using this slightly more complicated
version:

=IF(COUNTIF(ProductSelector!R:R,"X")=ROWS($B$1:B1 ),INDEX(ProductSelector!$I:$I,
SMALL(IF(ProductSelector!$R$12:$R$100="X",ROW(Prod uctSelector!$R$12:$R$100)),ROWS(B$1:B1))),"")

HTH,
Bernie
MS Excel MVP


"Lversteeg" wrote in message
...
I am hoping someone can help me!!!!

I have on one sheet a product selector for my sales team. I have added
a
column where they can put an "x" into if they would like to quote
that
product to a client. I have created another sheet called "Quote"
where the
prodcut details are automatically updated from the "Product
Selector".
However if they choose the products listed in ROW 1 and 3 they are
copied
the same way to the quote with blank lines in between. How can I
make sure
that they are copied over but without the blank lines

A
1 =IF(ProductSelector!R12="X",(ProductSelector!I12), "")
2 =IF(ProductSelector!R13="X",(ProductSelector!I13), "")
3 =IF(ProductSelector!R14="X",(ProductSelector!I14," ")

And so on, the problem is that if they put and "X" in B1 and B3 the
formula that is brought over
to the other worksheet is also in A1 and A3 and want to know if there
is something that I can add
to the formula so that if A2 is blank the result from A3 is moved to
there.