JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,532
- Office Version
- 365
- Platform
- Windows
This comes up fairly often, so I thought I'd see if there is a better way than the tedious way I am currently doing it.
The mini-sheet below is a table I use to track the NFL Power Rankings for the grandkids, who are big 49er fans. The new rankings come out every Tuesday. I need to make a copy of the 3 columns of the most recent section. The mini-sheet does not accurately display the table headings or the rows above the table. Those are formatted with the Center Across Selection alignment. This is how they really look.
I tried selecting columns E:G and Ctrl-Shift dragging them to the left. That gives me this error message.
Next I tried selecting just those three columns within the table. Dragging them results in all kinds of messes.
I tried right-clicking on E6 and selecting Insert | Table Columns to the Left. I got a new column, but the table was shifted to the right making the rows above the table misaligned.
I even tried copying the 3 columns outside the table and then back in, but got that same error message.
The only thing that sorta works is to insert 3 columns by right-clicking on the column sheet header (not the table header). Naturally, M$FT did not provide a way to insert 3 columns all at once, a feature that is in Google Sheets, so I have to do it 3 times. I can then copy the contents of the 3 columns of the previous section.
Does anyone know of a better way?
The mini-sheet below is a table I use to track the NFL Power Rankings for the grandkids, who are big 49er fans. The new rankings come out every Tuesday. I need to make a copy of the 3 columns of the most recent section. The mini-sheet does not accurately display the table headings or the rows above the table. Those are formatted with the Center Across Selection alignment. This is how they really look.
| 2022 Power Rankings.xlsx | ||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | |||
| 4 | Week 5 | Week 4 | Week 3 | Week 2 | Week 1 | Week 0 | ||||||||||||||||
| 5 | 10/11/22 | 10/04/22 | 9/27/22 | 9/20/22 | 9/13/22 | 9/09/22 | ||||||||||||||||
| 6 | Team | Conf | Division | Wk 5 Record | Wk 5 Rank | Wk 5 Δ | Wk 4 Record | Wk 4 Rank | Wk 4 Δ | Wk 3 Record | Wk 3 Rank | Wk 3 Δ | Wk 2 Record | Wk 2 Rank | Wk 2 Δ | Wk 1 Record | Wk 1 Rank | Wk 1 Δ | Wk 0 Record | Wk 0 Rank | ||
| 7 | Philadelphia Eagles | NFC | East | 5-0 | 2 | -1 | 4-0 | 1 | =0 | 3-0 | 1 | +2 | 2-0 | 3 | +7 | 1-0 | 10 | +4 | 0-0 | 14 | ||
| 8 | Buffalo Bills | AFC | East | 4-1 | 1 | +1 | 3-1 | 2 | =0 | 2-1 | 2 | -1 | 2-0 | 1 | =0 | 1-0 | 1 | =0 | 0-0 | 1 | ||
| 9 | Kansas City Chiefs | AFC | West | 4-1 | 3 | =0 | 3-1 | 3 | +2 | 2-1 | 5 | -3 | 2-0 | 2 | =0 | 1-0 | 2 | +5 | 0-0 | 7 | ||
| 10 | Green Bay Packers | NFC | North | 3-2 | 8 | -4 | 3-1 | 4 | =0 | 2-1 | 4 | +1 | 1-1 | 5 | +3 | 0-1 | 8 | -2 | 0-0 | 6 | ||
| 11 | San Francisco 49ers | NFC | West | 3-2 | 4 | +1 | 2-2 | 5 | +6 | 1-2 | 11 | -2 | 1-1 | 9 | +2 | 0-1 | 11 | -8 | 0-0 | 3 | ||
| 12 | Cincinnati Bengals | AFC | North | 2-3 | 9 | -3 | 2-2 | 6 | +4 | 1-2 | 10 | +2 | 0-2 | 12 | -3 | 0-1 | 9 | -4 | 0-0 | 5 | ||
| 13 | Baltimore Ravens | AFC | North | 3-2 | 5 | +2 | 2-2 | 7 | =0 | 2-1 | 7 | +3 | 1-1 | 10 | -4 | 1-0 | 6 | +4 | 0-0 | 10 | ||
| 14 | Tampa Bay Buccaneers | NFC | South | 3-2 | 6 | +2 | 2-2 | 8 | =0 | 2-1 | 8 | -4 | 2-0 | 4 | -1 | 1-0 | 3 | +1 | 0-0 | 4 | ||
| 15 | Minnesota Vikings | NFC | North | 4-1 | 7 | +2 | 3-1 | 9 | =0 | 2-1 | 9 | +2 | 1-1 | 11 | -4 | 1-0 | 7 | +13 | 0-0 | 20 | ||
| 16 | Miami Dolphins | AFC | East | 3-2 | 18 | -8 | 3-1 | 10 | -7 | 3-0 | 3 | +5 | 2-0 | 8 | +5 | 1-0 | 13 | +6 | 0-0 | 19 | ||
| 17 | Los Angeles Rams | NFC | West | 2-3 | 15 | -4 | 2-2 | 11 | -5 | 2-1 | 6 | =0 | 1-1 | 6 | -2 | 0-1 | 4 | -2 | 0-0 | 2 | ||
| 18 | Tennessee Titans | AFC | South | 3-2 | 11 | +1 | 2-2 | 12 | =0 | 2-1 | 12 | +8 | 0-2 | 20 | -3 | 0-1 | 17 | =0 | 0-0 | 17 | ||
| 19 | Dallas Cowboys | NFC | East | 4-1 | 10 | +3 | 3-1 | 13 | =0 | 2-1 | 13 | +8 | 1-1 | 21 | +6 | 0-1 | 27 | -12 | 0-0 | 15 | ||
| 20 | Los Angeles Chargers | AFC | West | 3-2 | 12 | +2 | 2-2 | 14 | +1 | 1-2 | 15 | -8 | 1-1 | 7 | -2 | 1-0 | 5 | +4 | 0-0 | 9 | ||
| 21 | Las Vegas Raiders | AFC | West | 1-4 | 14 | +1 | 1-3 | 15 | +6 | 0-3 | 21 | -6 | 0-2 | 15 | -1 | 0-1 | 14 | -3 | 0-0 | 11 | ||
| 22 | Denver Broncos | AFC | West | 2-3 | 23 | -7 | 2-2 | 16 | -2 | 2-1 | 14 | +3 | 1-1 | 17 | -1 | 0-1 | 16 | -8 | 0-0 | 8 | ||
| 23 | Arizona Cardinals | NFC | West | 2-3 | 19 | -2 | 2-2 | 17 | +3 | 1-2 | 20 | -6 | 1-1 | 14 | +8 | 0-1 | 22 | -6 | 0-0 | 16 | ||
| 24 | New Orleans Saints | NFC | South | 2-3 | 16 | +2 | 1-3 | 18 | +1 | 1-2 | 19 | -6 | 1-1 | 13 | -1 | 1-0 | 12 | +1 | 0-0 | 13 | ||
| 25 | New York Giants | NFC | East | 4-1 | 13 | +6 | 3-1 | 19 | +3 | 2-1 | 22 | -3 | 2-0 | 19 | +2 | 1-0 | 21 | +7 | 0-0 | 28 | ||
| 26 | Jacksonville Jaguars | AFC | South | 2-3 | 25 | -5 | 2-2 | 20 | -2 | 2-1 | 18 | +10 | 1-1 | 28 | +3 | 0-1 | 31 | -2 | 0-0 | 29 | ||
| 27 | Cleveland Browns | AFC | North | 2-3 | 20 | +1 | 2-2 | 21 | -4 | 2-1 | 17 | +5 | 1-1 | 22 | -2 | 1-0 | 20 | +2 | 0-0 | 22 | ||
| 28 | Altanta Falcons | NFC | South | 2-3 | 22 | =0 | 2-2 | 22 | +3 | 1-2 | 25 | +4 | 0-2 | 29 | +1 | 0-1 | 30 | +1 | 0-0 | 31 | ||
| 29 | Seattle Seahawks | NFC | West | 2-3 | 24 | -1 | 2-2 | 23 | +5 | 1-2 | 28 | -4 | 1-1 | 24 | -6 | 1-0 | 18 | +6 | 0-0 | 24 | ||
| 30 | Detroit Lions | NFC | North | 1-4 | 26 | -2 | 1-3 | 24 | -8 | 1-2 | 16 | =0 | 1-1 | 16 | +10 | 0-1 | 26 | -1 | 0-0 | 25 | ||
| 31 | New England Patriots | AFC | East | 2-3 | 21 | +4 | 1-3 | 25 | -1 | 1-2 | 24 | -6 | 1-1 | 18 | +5 | 0-1 | 23 | -5 | 0-0 | 18 | ||
| 32 | New York Jets | AFC | East | 3-2 | 17 | +9 | 2-2 | 26 | +5 | 1-2 | 31 | -6 | 1-1 | 25 | +7 | 0-1 | 32 | -6 | 0-0 | 26 | ||
| 33 | Pittsburgh Steelers | AFC | North | 1-4 | 27 | =0 | 1-3 | 27 | -1 | 2-1 | 26 | -3 | 1-1 | 23 | -4 | 1-0 | 19 | +2 | 0-0 | 21 | ||
| 34 | Indianapolis Colts | AFC | South | 2-2-1 | 28 | =0 | 1-2-1 | 28 | -5 | 1-1-1 | 23 | +3 | 0-1-1 | 26 | -11 | 0-0-1 | 15 | -3 | 0-0 | 12 | ||
| 35 | Washington Commanders | NFC | East | 1-4 | 30 | -1 | 1-3 | 29 | +1 | 1-2 | 30 | -3 | 1-1 | 27 | -3 | 1-0 | 24 | +6 | 0-0 | 30 | ||
| 36 | Chicago Bears | NFC | North | 2-3 | 29 | +1 | 2-2 | 30 | -3 | 1-2 | 27 | +3 | 101 | 30 | -5 | 1-0 | 25 | +7 | 0-0 | 32 | ||
| 37 | Carolina Panthers | NFC | South | 1-4 | 32 | -1 | 1-3 | 31 | -2 | 1-2 | 29 | +2 | 0-2 | 31 | -2 | 0-1 | 29 | -6 | 0-0 | 23 | ||
| 38 | Houston Texans | AFC | South | 1-3-1 | 31 | +1 | 0-3-1 | 32 | =0 | 0-2-1 | 32 | =0 | 0-1-1 | 32 | -4 | 0-0-1 | 28 | -1 | 0-0 | 27 | ||
2019 Power rankings | ||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G7:G38 | G7 | =[@[Wk 4 Rank]]-[@[Wk 5 Rank]] |
| J7:J38 | J7 | =[@[Wk 3 Rank]]-[@[Wk 4 Rank]] |
| M7:M38 | M7 | =[@[Wk 2 Rank]]-[@[Wk 3 Rank]] |
| P7:P38 | P7 | =[@[Wk 1 Rank]]-[@[Wk 2 Rank]] |
| S7:S38 | S7 | =[@[Wk 0 Rank]]-[@[Wk 1 Rank]] |
I tried selecting columns E:G and Ctrl-Shift dragging them to the left. That gives me this error message.
Next I tried selecting just those three columns within the table. Dragging them results in all kinds of messes.
I tried right-clicking on E6 and selecting Insert | Table Columns to the Left. I got a new column, but the table was shifted to the right making the rows above the table misaligned.
I even tried copying the 3 columns outside the table and then back in, but got that same error message.
The only thing that sorta works is to insert 3 columns by right-clicking on the column sheet header (not the table header). Naturally, M$FT did not provide a way to insert 3 columns all at once, a feature that is in Google Sheets, so I have to do it 3 times. I can then copy the contents of the 3 columns of the previous section.
Does anyone know of a better way?
I should have thought of that. My apologies to Microsoft! (This time)
