[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Moving Columns in Excel Tabe

Posted on 2009-04-21
6
Medium Priority
?
1,063 Views
Last Modified: 2012-05-06
I have an Excel table that consists of data being pull from various databases via odbc drivers.  This part is working fine.  What is happening is when I go to change the information in MS Query in Excel I get an error when I try to delete or add information to my table.  The message is as follows:
 "This operation is not allowed.  The operation is attempting to shift cells in a table on your worksheet"
Can someone tell me what I need to do to make changes in my table without having to start all over?
Thanks

0
Comment
Question by:nursecore
  • 2
  • 2
5 Comments
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 24196668
When you build your query, you just have to move up/down a column to set the order...
If you don't want to rebuild the whole thing, just place your cursor in a cell of this dataset, right-click with the mouse, and choose "Edit Query".  You will be able to set the order as you want
0
 

Author Comment

by:nursecore
ID: 24196982
I it when I use "Edit Query" and make changes inside "Edit Query" that I get this error.  I have been doing this in Excel 97 and 2003 for many years and have no problem moving columns, but in 2007 it creates a table and seems to get this error when I want to shift columns around and delete columns.  There has to be some setting that is preventing these changes or I am just doing something wrong.  Just a note in case it matters, the table is link to an Access database that was created to allow a Table from Foxpro and a Table from SQL to connect.
Thanks
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
ID: 24197114
Ok.  I thought that you were just selecting some columns in the spreadsheet, and tried to do a CUT & PASTE of a column to shift them.  But if you use the Edit Query, it should work.  But i'm using Excel 2003...   so sorry about 2007, but i can't help much.
0
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 2000 total points
ID: 24199013
nursecore,
Since you have been working with MS-Query for a long time you may have already taken these steps but I thought it might be a place to start...  I am assuming you have done all your data manipulation in ACCESS and are just bringing the resultset across to Excel via MS-Query.
From the DATA menu select PROPERTIES and uncheck the box that preserves columns/sort/etc... so the columns can be rearranged.  Then, on the MS-Query grid, delete all the field in the grid and then replace them from the Table box back into the grid in the order you want them to be displayed in the Excel sheet (table).  I ran several example just now and this worked without problems.  

MS-Query-Properties-1.jpg
0
 
LVL 16

Assisted Solution

by:Jerry Paladino
Jerry Paladino earned 2000 total points
ID: 24199027
I hit enter before I got the other two graphics attached...
MS-Query-Properties-2.jpg
MS-Query-Grid.jpg
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question