Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 25th 05, 11:45 PM
Orangepegs
 
Posts: n/a
Default 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?

  #2   Report Post  
Old February 26th 05, 01:19 AM
Max
 
Posts: n/a
Default

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,ROWS($A$1:A1))),"",IN DEX(Sheet1!A:A,MATCH(SMALL
(Sheet1!D,ROWS($A$1:A1)),Sheet1!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?



  #3   Report Post  
Old February 26th 05, 02:01 AM
CyberTaz
 
Posts: n/a
Default

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?

  #4   Report Post  
Old February 26th 05, 09:07 PM
Orangepegs
 
Posts: n/a
Default

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,ROWS($A$1:A1))),"",IN DEX(Sheet1!A:A,MATCH(SMALL
(Sheet1!D,ROWS($A$1:A1)),Sheet1!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?




  #5   Report Post  
Old February 26th 05, 11:11 PM
Max
 
Posts: n/a
Default

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?





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell Reference Math Ralph Howarth Excel Worksheet Functions 0 January 26th 05 06:27 PM
How do you delete a cell then shift cell up? Isela Excel Discussion (Misc queries) 4 January 22nd 05 01:41 AM
Shuffling Data To Make Lists Match Dmorri254 Excel Worksheet Functions 1 January 14th 05 05:58 PM
make a cell empty based on condition mpierre Charts and Charting in Excel 2 December 29th 04 01:01 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 07:16 PM


All times are GMT +1. The time now is 09:27 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017