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 :confused:


--
tmented
------------------------------------------------------------------------
tmented's Profile: http://www.excelforum.com/member.php...o&userid=26002
View this thread: http://www.excelforum.com/showthread...hreadid=393645

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

You'll find the following array formula:

=IF(ROW()-ROW(noblanksrange)+1ROWS(blanksrange)-
COUNTBLANK(blanksrange),"",INDIRECT(ADDRESS(SMALL(
(IF(blanksrange<"",ROW(blanksrange),ROW()+ROWS(bl anksrange))),
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)+1ROWS(blanksrange)-
COUNTBLANK(blanksrange),"",INDIRECT(ADDRESS(SMALL(
(IF(blanksrange<"",ROW(blanksrange),ROW()+ROWS(bl anksrange))),
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



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 :confused:


--
rgarber50
------------------------------------------------------------------------
rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350
View this thread: http://www.excelforum.com/showthread...hreadid=393645

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 :confused:


--
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" wrote in
message ...

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 :confused:


--
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" wrote in
message ...

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 :confused:


--
tmented
------------------------------------------------------------------------
tmented's Profile:
http://www.excelforum.com/member.php...o&userid=26002
View this thread: http://www.excelforum.com/showthread...hreadid=393645