selecting next available cell
 

I have a spreadsheet with a set of data in a list in just one coloumn.
this list however has some blanks in it.

What i want to do is on another sheet have a shortened version of this
list to avoid any blank cells appearing. Is there a formula i can use
to do this??

e.g current list :-

1

234

112

123

needed list

1
234
112
123

all i can think to do is select = and then type the reference of the
top cell, drag down until i have all the necessary data and then just
delete the rows where blank values appear. surely theres a way i can
avoid doing this?? please help!


--
girth69
------------------------------------------------------------------------
girth69's Profile: http://www.excelforum.com/member.php...o&userid=23634
View this thread: http://www.excelforum.com/showthread...hreadid=532316

selecting next available cell
 

i think there is a find next blank formula that may be suitable for this
but not sure what exactly it is??


--
girth69
------------------------------------------------------------------------
girth69's Profile: http://www.excelforum.com/member.php...o&userid=23634
View this thread: http://www.excelforum.com/showthread...hreadid=532316

selecting next available cell
 

If your data is in column A with a label cell at the top, then in column B,
cell B2 enter:

=(A2="") and copy down.

Notice that if a cell in A is blank, then the cell in B is TRUE.

Pull-down:
Data Filter Autofilter

In column B pull-down FALSE and all the blanks (in column A) will be hidden.
Copy column A and paste where you will.
--
Gary's Student


"girth69" wrote:


I have a spreadsheet with a set of data in a list in just one coloumn.
this list however has some blanks in it.

What i want to do is on another sheet have a shortened version of this
list to avoid any blank cells appearing. Is there a formula i can use
to do this??

e.g current list :-

1

234

112

123

needed list

1
234
112
123

all i can think to do is select = and then type the reference of the
top cell, drag down until i have all the necessary data and then just
delete the rows where blank values appear. surely theres a way i can
avoid doing this?? please help!


--
girth69
------------------------------------------------------------------------
girth69's Profile: http://www.excelforum.com/member.php...o&userid=23634
View this thread: http://www.excelforum.com/showthread...hreadid=532316

selecting next available cell
 

Copy the list to your other sheet. In this other sheet, select the whole
range from top to bottom. Press the F5 key, click on Special, click on
Blanks, click OK. Click on Edit - Delete - Shift Cells Up. Done
HTH Otto
"girth69" wrote in
message ...

I have a spreadsheet with a set of data in a list in just one coloumn.
this list however has some blanks in it.

What i want to do is on another sheet have a shortened version of this
list to avoid any blank cells appearing. Is there a formula i can use
to do this??

e.g current list :-

1

234

112

123

needed list

1
234
112
123

all i can think to do is select = and then type the reference of the
top cell, drag down until i have all the necessary data and then just
delete the rows where blank values appear. surely theres a way i can
avoid doing this?? please help!


--
girth69
------------------------------------------------------------------------
girth69's Profile:
http://www.excelforum.com/member.php...o&userid=23634
View this thread: http://www.excelforum.com/showthread...hreadid=532316

selecting next available cell
 

Use Otto's method. You won't need to disturb the original worksheet in any
way.
--
Gary''s Student


"girth69" wrote:


i think there is a find next blank formula that may be suitable for this
but not sure what exactly it is??


--
girth69
------------------------------------------------------------------------
girth69's Profile: http://www.excelforum.com/member.php...o&userid=23634
View this thread: http://www.excelforum.com/showthread...hreadid=532316

selecting next available cell
 

that doesnt work if im trying to link the cells in the sheet to the new
desired sheet. i can copy the values across no problems but i need the
cells to be linked in. can this be done??


--
girth69
------------------------------------------------------------------------
girth69's Profile: http://www.excelforum.com/member.php...o&userid=23634
View this thread: http://www.excelforum.com/showthread...hreadid=532316