selecting next available cell

[girth69]

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]

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

 

[Guest]

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.

 

[Otto Moehrbach]

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

 

[Guest]

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

 

[girth69]

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??