JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,676
- 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?