I have a list of data which is created using various lookup's and if
statements from information entered into 6 cells seperate to the list. Some
cells in the list are not used depening on the information entered, and the
position of the cells that are not used varies. Therefore, I have a list
where blanks can appear in any cell. My list is on the second sheet of my
workbook, and I want the list to appear on the front sheet (below headers
etc,) but without blanks. I actually want the list to appear with a blank
line inbetween each cell of data, but i might be able to add that later.

Does anybody have any idea of how to show a list in another location that
will effectivly update, as the inforation used to write the list is changed.
I have been trying to write a formula that will show a cell if it has text in
it, but that will show the next cell if that cell is blank, and then the next
cell if that cell is blank and so on, but then the next cell has to skip to
the cell following the one that has just been shown?

If more clarification is required please ask.

Try something like this:

With
Your original list on Sheet1, cells A1:A10 (containing some blank cells)

Then
On Sheet2
B1: List

This ARRAY FORMULA* lists unique, non-blank items from the Sheet1 list.
B2:
=IF(SUMPRODUCT((Sheet1!$A$1:$A$10<"")*ISERROR(MAT CH(Sheet1!$A$1:$A$10,$B$1:B1,0)))<0,INDEX(Sheet1! $A$1:$A$10,MATCH(TRUE,ISERROR(IF(ISBLANK(Sheet1!$A $1:$A$10),FALSE,MATCH(Sheet1!$A$1:$A$10,$B$1:$B1,0 ))),0),1),"")

*Note_1: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Note_2: Since text wrap will impact the display, there are NO spaces in that
formula.

Copy cell B2
Paste into B4, B6, B8, etc.... and down as far as you need.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"hot dogs" wrote:

I have a list of data which is created using various lookup's and if
statements from information entered into 6 cells seperate to the list. Some
cells in the list are not used depening on the information entered, and the
position of the cells that are not used varies. Therefore, I have a list
where blanks can appear in any cell. My list is on the second sheet of my
workbook, and I want the list to appear on the front sheet (below headers
etc,) but without blanks. I actually want the list to appear with a blank
line inbetween each cell of data, but i might be able to add that later.

Does anybody have any idea of how to show a list in another location that
will effectivly update, as the inforation used to write the list is changed.
I have been trying to write a formula that will show a cell if it has text in
it, but that will show the next cell if that cell is blank, and then the next
cell if that cell is blank and so on, but then the next cell has to skip to
the cell following the one that has just been shown?

If more clarification is required please ask.

Thanks,
It seams to work quite well, but i have a blank on the 4th cell
down of my list and that blank was projected into the new list, not sure why.

"Ron Coderre" wrote:

Try something like this:

With
Your original list on Sheet1, cells A1:A10 (containing some blank cells)

Then
On Sheet2
B1: List

This ARRAY FORMULA* lists unique, non-blank items from the Sheet1 list.
B2:
=IF(SUMPRODUCT((Sheet1!$A$1:$A$10<"")*ISERROR(MAT CH(Sheet1!$A$1:$A$10,$B$1:B1,0)))<0,INDEX(Sheet1! $A$1:$A$10,MATCH(TRUE,ISERROR(IF(ISBLANK(Sheet1!$A $1:$A$10),FALSE,MATCH(Sheet1!$A$1:$A$10,$B$1:$B1,0 ))),0),1),"")

*Note_1: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Note_2: Since text wrap will impact the display, there are NO spaces in that
formula.

Copy cell B2
Paste into B4, B6, B8, etc.... and down as far as you need.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"hot dogs" wrote:

I have a list of data which is created using various lookup's and if
statements from information entered into 6 cells seperate to the list. Some
cells in the list are not used depening on the information entered, and the
position of the cells that are not used varies. Therefore, I have a list
where blanks can appear in any cell. My list is on the second sheet of my
workbook, and I want the list to appear on the front sheet (below headers
etc,) but without blanks. I actually want the list to appear with a blank
line inbetween each cell of data, but i might be able to add that later.

Does anybody have any idea of how to show a list in another location that
will effectivly update, as the inforation used to write the list is changed.
I have been trying to write a formula that will show a cell if it has text in
it, but that will show the next cell if that cell is blank, and then the next
cell if that cell is blank and so on, but then the next cell has to skip to
the cell following the one that has just been shown?

If more clarification is required please ask.

but i have a blank on the 4th cell down of my list and that blank was
projected into the new list, not sure why<<

My guess.....it's not really blank. It may contain an apostrophe or a space.
Try this:
if A4 is the "blank" cell in the original list.
B4: =ISBLANK(A4)
FALSE means not-blank.

***********
Regards,
Ron

XL2002, WinXP


"hot dogs" wrote:

Thanks,
It seams to work quite well, but i have a blank on the 4th cell
down of my list and that blank was projected into the new list, not sure why.

"Ron Coderre" wrote:

Try something like this:

With
Your original list on Sheet1, cells A1:A10 (containing some blank cells)

Then
On Sheet2
B1: List

This ARRAY FORMULA* lists unique, non-blank items from the Sheet1 list.
B2:
=IF(SUMPRODUCT((Sheet1!$A$1:$A$10<"")*ISERROR(MAT CH(Sheet1!$A$1:$A$10,$B$1:B1,0)))<0,INDEX(Sheet1! $A$1:$A$10,MATCH(TRUE,ISERROR(IF(ISBLANK(Sheet1!$A $1:$A$10),FALSE,MATCH(Sheet1!$A$1:$A$10,$B$1:$B1,0 ))),0),1),"")

*Note_1: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Note_2: Since text wrap will impact the display, there are NO spaces in that
formula.

Copy cell B2
Paste into B4, B6, B8, etc.... and down as far as you need.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"hot dogs" wrote:

I have a list of data which is created using various lookup's and if
statements from information entered into 6 cells seperate to the list. Some
cells in the list are not used depening on the information entered, and the
position of the cells that are not used varies. Therefore, I have a list
where blanks can appear in any cell. My list is on the second sheet of my
workbook, and I want the list to appear on the front sheet (below headers
etc,) but without blanks. I actually want the list to appear with a blank
line inbetween each cell of data, but i might be able to add that later.

Does anybody have any idea of how to show a list in another location that
will effectivly update, as the inforation used to write the list is changed.
I have been trying to write a formula that will show a cell if it has text in
it, but that will show the next cell if that cell is blank, and then the next
cell if that cell is blank and so on, but then the next cell has to skip to
the cell following the one that has just been shown?

If more clarification is required please ask.