Forum Discussion
#ref! when trying to copy paste
ok, it's hard to explain.. I would like to be able to move rows of names from one sheet to different sheets. But I would like a main sheet where all the names are alphabetical and where I can sort different columns in that main sheet. So, when I take a name that is in one cell from sheet A and paste in sheet B.
| Betts | Mookie | 2B | BOS | 9 | 4 | 0 | 16 | 0 | bears |
| #REF! | #REF! | #### | #REF! | #REF! | #REF! | #REF! | #REF! | 0 | brainiacs |
so in above example, this is what would be the main sheet. There are no formulas, just reading cells from other sheets within the workbook. So where Betts is now he should be where I'm getting the #ref! error. and that row should be blank.
basically it's a fantasy baseball league. Each sheet contains all the players for one team, there are 12 sheets. And I also have a main sheet that contains all of the players. It's all good unless there is a trade and I need to move a player from one sheet to another. Then the Main sheet gets this Ref error when I do move them.
Hope that makes sense.
Yes, #REF! appears if you paste over the cell on which you have the reference. As i understood you need to move the row (which is actually the record about the player) from one sheet to another.
To avoid paste over i'd suggest
1) select entire row in first sheet
2) right click menu - Cut
3) select any row in the second sheet
4) right click menu - Insert Cut Cells
5) return to first sheet and delete empty row
ok, the only problem with that is I have columns to the Right of that info that is necessary to keep the web page formatted correctly.
Is there a command or a way that I would be able to have 1 main sheet of players and all other sheets feed automatically from that sheet, I know that is easy to do with the "=" command but presently I can find no way to do that and the info travels succssfully. Example; Joe Blow is listed in Sheet 1 (main sheet with All players), and he is automatically listed in cell A1;Sheet 2 (easy to do with the "=" command), but then if traded to another team, is there a command or a way to let that player automatically go into SHeet 3. I've tried copy and pasting from Sheet 2, I've tried listing team names in last column and sorting in Sheet 1, but the player either doesn't travel to the new Sheet.
If you have column to the right which shall not be changed please do the same, but select not entire row but only columns for this row which are with the information you'd like to move (both for Cut and Insert Cut Cells). Excel will ask shift cells Up (Down), just confirm what needed.
If in opposite way, keep all data about players in first sheet and after that distribute them to other sheets, simple filtering solution (assuming in players list you'll add the column which indicates in which team the player is now) won't work since you have that column which shall not be changed. You may filter outside the sheet, e.g. pick-up your players data by Get&Transform (Power Query), filter it in the query and return to the team sheet as filtered table.
Afraid no solution with simple references for such case.
