selecting a formula to?

Hi I was wondering how I would go about performing this operation.
I have a list of Items set up in sheet 1, column A, all of them are text.
in column b I would enter a quantity.
in sheet 2 I want collumn A to reflect the list from sheet 1 but only if there is a value in collumn B.
this is easy enough to do using an if statement but I would like to fix it so that if
row 1 doesn't have a value it looks at row 2 and so on The purpose is to eliminate empty rows in sheet 2.
Can anyone help with this
Thanks
Lisa

Check out Chip Pearsons site:
http://www.cpearson.com/excel/noblanks.htm

You'll find the following array formula:

=IF(ROW()-ROW(noblanksrange)+1>ROWS(blanksrange)- COUNTBLANK(blanksrange),"",INDIRECT(ADDRESS(SMALL( (IF(blanksrange<>"",ROW(blanksrange),ROW()+ROWS(blanksrange))), ROW()-ROW(noblanksrange)+1),COLUMN(blanksrange),4)))

Define the following ranges:
labelsrange is the range on wks1 that contains the text label entries.
blanksrange is the range on wks 1 column B - the range of cells that will have numeric values entered into them - some of the cells will remain empty.
noblanksrange is the corresponding range on wks 2 col B - has to be the same size range as blanksrange on wks1 and starting on the same row.

Copy the above formula to the first cell of noblanksrange on wks2. Now double click on the cell and enter it as an array using ctrl-shift-enter. Now highlight noblanksrange and fill down. This should give you the list of values without the empty rows.

In Wks 2 Col A copy the following formula - which is a variation on mr. pearson's:

=IF(ROW()-ROW(noblanksrange)+1>ROWS(blanksrange)- COUNTBLANK(blanksrange),"",INDIRECT(ADDRESS(SMALL( (IF(blanksrange<>"",ROW(blanksrange),ROW()+ROWS(blanksrange))), ROW()-ROW(noblanksrange)+1),COLUMN(labelsrange),4)))

Enter it as an array as described above.

The only difference is instead of extracting the numeric values in col b it extracts the labels in col A.

Seems to be working for me. Hope it helps.

Richard

Originally Posted by tmented

Hi I was wondering how I would go about performing this operation.
I have a list of Items set up in sheet 1, column A, all of them are text.
in column b I would enter a quantity.
in sheet 2 I want collumn A to reflect the list from sheet 1 but only if there is a value in collumn B.
this is easy enough to do using an if statement but I would like to fix it so that if
row 1 doesn't have a value it looks at row 2 and so on The purpose is to eliminate empty rows in sheet 2.
Can anyone help with this
Thanks
Lisa

Assume your text entries are in Sheet 1, cells A1 to A6.
On a third sheet, Sheet 3:
A1: =ROW(Sheet1!A1)-ROW(Sheet1!$A$1)+1
B1: =Sheet1!A1
C1: =IF(ISBLANK(Sheet1!B1)=FALSE,Sheet1!B1,9E+99)
D1: =SUMPRODUCT(--($A$1:$A$6<=A1),--($C$1:$C$6<>9E+99))
E1: =SUMPRODUCT($A$1:$A$6,--($D$1:$D$6=A1),--($C$1:$C$6<>9E+99))
F1: =VLOOKUP($E1,$A$1:$B$6,2)
G1: =VLOOKUP($E1,$A$1:$C$6,3)
Copy A1:G1 to A1:G6
Sheet 2:
A1: =IF(ISERROR(Sheet3!F1)=TRUE,"",Sheet3!F1)
B1: =IF(ISERROR(Sheet3!G1)=TRUE,"",Sheet3!G1)
Copy to A1:B6

"tmented" wrote:

>
> Hi I was wondering how I would go about performing this operation.
> I have a list of Items set up in sheet 1, column A, all of them are
> text.
> in column b I would enter a quantity.
> in sheet 2 I want collumn A to reflect the list from sheet 1 but only
> if there is a value in collumn B.
> this is easy enough to do using an if statement but I would like to fix
> it so that if
> row 1 doesn't have a value it looks at row 2 and so on The purpose is
> to eliminate empty rows in sheet 2.
> Can anyone help with this
> Thanks
> Lisa
>
>
> --
> tmented
> ------------------------------------------------------------------------
> tmented's Profile: http://www.excelforum.com/member.php...o&userid=26002
> View this thread: http://www.excelforum.com/showthread...hreadid=393645
>
>

Perhaps a non-array formulas play which will deliver exactly what you're
after ?

In Sheet1, data is assumed to be in cols A and B, from row1 down

Using an empty adjacent col, say col C
Put in C1: =IF(B1="","",ROW())
Copy C1 down to say, C100, to cover the max expected data in cols A and B

In Sheet2
-----
Put in A1:

=IF(ISERROR(SMALL(Sheet1!$C:$C,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$C:$C,ROWS($A$1:A1)),Sheet1!$C:$C,0)))

Copy A1 across to B1, fill down to B100
(cover the same range as was done in Sheet1's col C)

Sheet2 will return the desired results all neatly bunched at the top,
with blank rows below

Here's a sample file with the implemented example:
http://www.savefile.com/files/7108118
File: Selecting a formula to_tmented_misc.xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
Sample files at: http://savefile.com/projects/236895
----
"tmented" <[email protected]> wrote in
message news:[email protected]...
>
> Hi I was wondering how I would go about performing this operation.
> I have a list of Items set up in sheet 1, column A, all of them are
> text.
> in column b I would enter a quantity.
> in sheet 2 I want collumn A to reflect the list from sheet 1 but only
> if there is a value in collumn B.
> this is easy enough to do using an if statement but I would like to fix
> it so that if
> row 1 doesn't have a value it looks at row 2 and so on The purpose is
> to eliminate empty rows in sheet 2.
> Can anyone help with this
> Thanks
> Lisa
>
>
> --
> tmented
> ------------------------------------------------------------------------
> tmented's Profile:
http://www.excelforum.com/member.php...o&userid=26002
> View this thread: http://www.excelforum.com/showthread...hreadid=393645
>

When sheet2 is in need of update,
Copy the entire Sheet1 to Sheet2
then (with sheet2 as active sheet, Highlight/Select B1:B100 (from 1st to
last)
and run:

Sub Macro1()
'
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("A1").Select
End Sub

HTH



"tmented" <[email protected]> wrote in
message news:[email protected]...
>
> Hi I was wondering how I would go about performing this operation.
> I have a list of Items set up in sheet 1, column A, all of them are
> text.
> in column b I would enter a quantity.
> in sheet 2 I want collumn A to reflect the list from sheet 1 but only
> if there is a value in collumn B.
> this is easy enough to do using an if statement but I would like to fix
> it so that if
> row 1 doesn't have a value it looks at row 2 and so on The purpose is
> to eliminate empty rows in sheet 2.
> Can anyone help with this
> Thanks
> Lisa
>
>
> --
> tmented
> ------------------------------------------------------------------------
> tmented's Profile:
> http://www.excelforum.com/member.php...o&userid=26002
> View this thread: http://www.excelforum.com/showthread...hreadid=393645
>