Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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? |
#3
|
|||
|
|||
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
|
|||
|
|||
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? |
#5
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Reference Math | Excel Worksheet Functions | |||
How do you delete a cell then shift cell up? | Excel Discussion (Misc queries) | |||
Shuffling Data To Make Lists Match | Excel Worksheet Functions | |||
make a cell empty based on condition | Charts and Charting in Excel | |||
GET.CELL | Excel Worksheet Functions |