LinkBack URL
About LinkBacks
When updating MS Access Query, tables connected in excel changes. Usually the field that has been edited will go at the last column. How to deal with it? Thanks.
When updating MS Access Query, tables connected in excel changes. Usually the field that has been edited will go at the last column. How to deal with it? Thanks.
Last edited by dummy777; 02-22-2019 at 11:08 AM. Reason: Solved
I noticed that too. I think it's just the way MS-Query works: the changed column winds up in the last position regardless of where it is in the query select statement. You can move the column in excel manually and it will stay in place.
Normally this is not an issue since MS-Query returns data to Excel Tables and you can used column header names for formulas, charts and pivot tables.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Thank you for your response. Now I'm using index match functions as I have a template. However you have a great suggestion to used column header names for formula and to move the column in excel manually, I haven't thought of that. Also, does moving the column manually in excel will still stay in place even if I refresh the data?
Yes, once you move the column, it will stay put even after a refresh. Any "helper columns" with formulas will always get moved to the end, but returned data will stay where you put it.
Here is some more information on tables: http://www.utteraccess.com/wiki/Tables_in_Excel
As for index / match you can try =INDEX(Table_Name, Match(whatever, Table_Name[Column_Name],0),Match(Other_Column_Name,Table_Name[#Headers],0))
It doesn't matter where Other_Column_Name winds up.
Last edited by dflak; 02-22-2019 at 09:46 AM.
Thank you very much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks