How can I make cell data automatically shift up when deleting
 

EXAMPLE:

A B C
1) Dog Bone Leash
2) Cat Catnip Litter
3) Bird Seed Newspaper
4) Cow Grass Manuer
5) Duck Fish Filter
6) Mouse Cheese Hanta Virus

If I were to delete the information in row 1, I want everything in row two
to shift up automatically. Or... If I were to delete "dog," I would want
everything in Column A, and only Column A, to shift up, therefore removing
any blank cells in the table. How would I do this?

One way ..

Assuming this table is in Sheet1, cols A to C,
data from row1 down

A B C
1) Dog Bone Leash
2) Cat Catnip Litter
3) Bird Seed Newspaper
etc

Put in D1: =IF(A1="","",ROW())

Copy D1 across to F1, fill down to say, F1000
to cover the max expected data range in cols A to C

In Sheet2
-------------
Put in say, A2:

=IF(ISERROR(SMALL(Sheet1!D:D,ROWS($A$1:A1))),"",IN DEX(Sheet1!A:A,MATCH(SMALL
(Sheet1!D:D,ROWS($A$1:A1)),Sheet1!D:D,0)))

Copy A2 across to C2, fill down to C1001
(cover same range as in Sheet1)

Sheet2 will return the desired behaviour
depending on changes made in Sheet1
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Orangepegs" wrote in message
...
EXAMPLE:

A B C
1) Dog Bone Leash
2) Cat Catnip Litter
3) Bird Seed Newspaper
4) Cow Grass Manuer
5) Duck Fish Filter
6) Mouse Cheese Hanta Virus

If I were to delete the information in row 1, I want everything in row two
to shift up automatically. Or... If I were to delete "dog," I would want
everything in Column A, and only Column A, to shift up, therefore removing
any blank cells in the table. How would I do this?

All the coding is impressive and I'm sure it works like a charm, but I prefer
EditDeleteShift Cells Up, myself |:)

"Orangepegs" wrote:

EXAMPLE:

A B C
1) Dog Bone Leash
2) Cat Catnip Litter
3) Bird Seed Newspaper
4) Cow Grass Manuer
5) Duck Fish Filter
6) Mouse Cheese Hanta Virus

If I were to delete the information in row 1, I want everything in row two
to shift up automatically. Or... If I were to delete "dog," I would want
everything in Column A, and only Column A, to shift up, therefore removing
any blank cells in the table. How would I do this?

That is very impressive coding, and it does work. However, I was hoping for
something a bit more two diminsional. I want it so when I make corrections on
the first page, the cells shift up on the first page. The "edit, delete,
shift up" works fine, and that may be what I have to resort to. However, I am
trying to create a spreadsheet for a complete computer novice, and that may
be too many steps. If there is a function, code, or setting that could make
that possible without getting too complicated in the dismount, that would be
ideal. Any other ways to do this?

"Max" wrote:

One way ..

Assuming this table is in Sheet1, cols A to C,
data from row1 down

A B C
1) Dog Bone Leash
2) Cat Catnip Litter
3) Bird Seed Newspaper
etc

Put in D1: =IF(A1="","",ROW())

Copy D1 across to F1, fill down to say, F1000
to cover the max expected data range in cols A to C

In Sheet2
-------------
Put in say, A2:

=IF(ISERROR(SMALL(Sheet1!D:D,ROWS($A$1:A1))),"",IN DEX(Sheet1!A:A,MATCH(SMALL
(Sheet1!D:D,ROWS($A$1:A1)),Sheet1!D:D,0)))

Copy A2 across to C2, fill down to C1001
(cover same range as in Sheet1)

Sheet2 will return the desired behaviour
depending on changes made in Sheet1
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Orangepegs" wrote in message
...
EXAMPLE:

A B C
1) Dog Bone Leash
2) Cat Catnip Litter
3) Bird Seed Newspaper
4) Cow Grass Manuer
5) Duck Fish Filter
6) Mouse Cheese Hanta Virus

If I were to delete the information in row 1, I want everything in row two
to shift up automatically. Or... If I were to delete "dog," I would want
everything in Column A, and only Column A, to shift up, therefore removing
any blank cells in the table. How would I do this?

Sorry, am out of ideas ..
Hang around awhile for better insights from others
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Orangepegs" wrote in message
...
That is very impressive coding, and it does work. However, I was hoping
for
something a bit more two diminsional. I want it so when I make corrections
on
the first page, the cells shift up on the first page. The "edit, delete,
shift up" works fine, and that may be what I have to resort to. However, I
am
trying to create a spreadsheet for a complete computer novice, and that
may
be too many steps. If there is a function, code, or setting that could
make
that possible without getting too complicated in the dismount, that would
be
ideal. Any other ways to do this?