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