hi,

we have an excel file that gets info from a SQL Server, this is all
fine and works well taking data from two tables on our SQL Server, the
problem is that when a user adds a comment to a cell or enters data
into a field that isnt on SQL server but is used on the excel file,
this information does not stay associated with that particlar record
(although it isnt an actual record on SQL Server because its built from
2 tables) any ideas on how to fix to stop the data from SQL "jumping"
around.

Short answer: don't do this, it doesn't even make sense from a Data
model perspective.

Long answer:

incorporating client side data that should be persistent with a report
on is not the way to do Information handling.

That said you can do the following to work around that:

1. Decide on which identifier field the comment is based, a customer
number, order number,invoice number?

2 on a new sheet make a table of 2 columns, column 1 contains the
field, and column 2 contains the comment/data.

3. besides your SQL report, fill down a lookup formula that looks up
the comment given the field value.

4. Select Data Range properties and tick "fill formulas adjectant to
cells"


Don't use Excel "Comments" because they are position oriented, while
the SQL query is data oriented.

DM Unseen

Hi

You must enter those comments to separate table - together with key value
from query table. Into query table, you add a column to right of last column
returned by query, where VLOOKUP function is used to display comments
accordingly key values. In query properties, 'Refresh formulas in adjacent
columns' must be checked.

One possible setup - you define both your query result table and it's key
column as dynamic named ranges (p.e. qTable, qKey). On another sheet (p.e.
Comments), you create a table KeyValue, Comment - with KeyValue defined as
data validation list with qKey as source (probably you have to allow
not-in-list values - otherwise you may have problems with key values from
older sessions), and define a dynamic named range p.e. Comments, based on
this table. Also you may use the conditional formatting to mark all rows in
table with key value used earlier, p.e. with different color - VLOOKUP
always returns result from row with first occurrence of searched key value,
so the user needs to be warned about it. To right of query result table you
add a column Comments, where the appropriate comment from Comments sheet is
displayed.


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



wrote in message
ups.com...
hi,

we have an excel file that gets info from a SQL Server, this is all
fine and works well taking data from two tables on our SQL Server, the
problem is that when a user adds a comment to a cell or enters data
into a field that isnt on SQL server but is used on the excel file,
this information does not stay associated with that particlar record
(although it isnt an actual record on SQL Server because its built from
2 tables) any ideas on how to fix to stop the data from SQL "jumping"
around.