Sticky sorting/macro/VLOOKUP problem

[Ron M.]

I've tried to find the answer to this here and elsewhere, but no luck,
although there's quite a bit on "sorting with blank lines."

I have a spreadsheet with 10,000 blank lines and several columns, A
through E. It's on a networked server, and various people in the office
open it and enter data into it, filling it in from the top.

In Column E, their last names are automatically entered from a table
using VLOOKUP, matched to data they enter in one of the other columns.

When I try to sort data by the names in Column E, in ascending order,
it does so, but shoves it all down to the BOTTOM of the spreadsheet,
with the blank lines on top. Keep in mind that each of the cells in
Column E contains the VLOOKUP formula.

I need to make a "sort by name" macro my supervisor can activate with a
button. Is there some way to make this work so the data stays at the
top of the spreadsheet?

Ron M.

 

[Dave Peterson]

Maybe you could change the formula:

=if(b2="","",vlookup(....))
to something like:
=if(b2="","zzzzzzzzzzzzzzz",vlookup(....))

Then use format|conditional formatting to make that zzzzz stuff look invisible
(white font on white fill???).

=======
Or....

Maybe you could add a helper column
=if(g2="","zzzzzzzzzzzzzz",g2)
and drag down

Then sort by this helper column.