(Msg. 1) Posted: Thu Mar 29, 2007 8:24 am
Post subject: Excel 12: Question about refreshing data connections Archived from groups: microsoft>public>excel>querydao (more info?)
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?
(Msg. 2) Posted: Sat Mar 31, 2007 7:01 am
Post subject: Re: Excel 12: Question about refreshing data connections [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Ferris
Not tried it, but you might try setting it up as a table in Excel (Insert
tab)
"Ferris" <kochjeff RemoveThis @gmail.com> wrote in message
news:1175181847.710042.119700@d57g2000hsg.googlegroups.com...
>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!
>
(Msg. 3) Posted: Tue Apr 03, 2007 4:16 pm
Post subject: Re: Excel 12: Question about refreshing data connections [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
On 29 Mar 2007 08:24:07 -0700, "Ferris" <kochjeff RemoveThis @gmail.com> wrote:
>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
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
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office