Excel 12: Question about refreshing data connections "Ferris" <kochjeff[ at ]gmail.com> 3/29/2007 3:24:07 PM I have a worksheet that contains a table linked to a SQL query. I have added some columns to the sheet which contain additional information for each row in the table through a series of macros. If I refresh the data connection to pull in the latest items from the SQL table it throws off all of the data in the columns I have manually added. Even if I make sure my worksheet is sorted in the same way my SQL query sorts the data I have the same problem. All of the additional information I have added gets misaligned. Is there a way to manage the addition of manually entered data in to a query-based table successfully? Thanks!

Re: Excel 12: Question about refreshing data connections "Nick Hodge" <nick_hodgeTAKETHISOUT[ at ]zen.co.uk.ANDTHIS> 3/31/2007 10:22:45 AM Ferris Not tried it, but you might try setting it up as a table in Excel (Insert tab) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT[ at ]zen.co.ukANDTHIS web: www.nickhodge.co.uk blog: www.nickhodge.co.uk/blog/ FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007 www.officeusergroup.co.uk "Ferris" <kochjeff[ at ]gmail.com> wrote in message news:1175181847.710042.119700[ at ]d57g2000hsg.googlegroups.com... [Quoted Text] >I have a worksheet that contains a table linked to a SQL query. I have > added some columns to the sheet which contain additional information > for each row in the table through a series of macros. If I refresh the > data connection to pull in the latest items from the SQL table it > throws off all of the data in the columns I have manually added. Even > if I make sure my worksheet is sorted in the same way my SQL query > sorts the data I have the same problem. All of the additional > information I have added gets misaligned. > > Is there a way to manage the addition of manually entered data in to a > query-based table successfully? > > Thanks! >

Re: Excel 12: Question about refreshing data connections Dick Kusleika <dkusleika[ at ]gmail.com> 4/3/2007 9:16:06 PM On 29 Mar 2007 08:24:07 -0700, "Ferris" <kochjeff[ at ]gmail.com> wrote: [Quoted Text] >I have a worksheet that contains a table linked to a SQL query. I have >added some columns to the sheet which contain additional information >for each row in the table through a series of macros. If I refresh the >data connection to pull in the latest items from the SQL table it >throws off all of the data in the columns I have manually added. Even >if I make sure my worksheet is sorted in the same way my SQL query >sorts the data I have the same problem. All of the additional >information I have added gets misaligned. > >Is there a way to manage the addition of manually entered data in to a >query-based table successfully? > There is, but it's not necessarily easy. Start here http://www.dicks-blog.com/archives/2004/04/26/data-range-properties-options/ That will give you some background on what Excel is doing when you refresh an external data table. Because rows are deleted or blanked or whatever, you can't rely on contiguous data being where you want. The proper way to do this is to create a separate table, preferably on a separate sheet. Make sure you include the key field from the external data table in your secondary table. Now use formulas (VLOOKUP, I would guess) to retrieve the information from the new table and show it adjacent to the external data tables. If you make sure there are no blank columns, you can use 'Fill Down Formulas' as described here http://www.dicks-blog.com/archives/2004/04/26/data-range-properties-options/ to ensure the formulas are in all the same rows as the external data. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com