Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 -- tmented ------------------------------------------------------------------------ tmented's Profile: http://www.excelforum.com/member.php...o&userid=26002 View this thread: http://www.excelforum.com/showthread...hreadid=393645 |
#2
|
|||
|
|||
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 -- rgarber50 ------------------------------------------------------------------------ rgarber50's Profile: http://www.excelforum.com/member.php...o&userid=11350 View this thread: http://www.excelforum.com/showthread...hreadid=393645 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 -- tmented ------------------------------------------------------------------------ tmented's Profile: http://www.excelforum.com/member.php...o&userid=26002 View this thread: http://www.excelforum.com/showthread...hreadid=393645 |
#5
|
|||
|
|||
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 -- tmented ------------------------------------------------------------------------ tmented's Profile: http://www.excelforum.com/member.php...o&userid=26002 View this thread: http://www.excelforum.com/showthread...hreadid=393645 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simplify formula | Excel Worksheet Functions | |||
EDIT FORMULA BAR in excel 2003? why not? where is it? | Excel Worksheet Functions | |||
put formula results into a different cell if it is empty | Excel Worksheet Functions | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |