We help IT Professionals succeed at work.

Excel database link keep rows aligned

stealthwifi12
stealthwifi12 used Ask the Experts™
on
Ok I have a hard time explaining this one so bare with me:

I have an Excel 2007 Workbook with one Sheet (Form) with an outside data connection (MySQL database) The data from the database is dumped in rows B-G. What I need is a way to mark say cell A2 and have that stay with the data in that row, so if A5 contained the word Ordered and B2-G2 contained the data from the database and someone removed that data it would throw off the rows making A5 next to what used to be B-G6 (since the values from B5 where removed)

How do I keep things aligned, is there an excel trick or some VBA that can keep things in order?
(I can pull in the unique ID field from the database table if that would help)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I'm not quite understanding what you're wanting.  Could you provide a sample workbook with static values?  Maybe looking at it would help.

Author

Commented:
I'll try and get a workbook put togather

Basically if if have the below layout:

  a     b     c     d
 bob jim same jo
 jim same bob joe
bob1 same up down

And B-D are values put in from a database then when someone deletes row 2 from the database my excel sheet looks like this:

  a   b    c    d
bob jim same jo
jim same up down
bob1
The best solution would be to create a field in the database for column A and query it along with the rest......
Unless there some kind of reference within the database values that column A could reference, I'm not sure.....
For example, if column A were employees and the table had an Emp_Id field, you could use some code or reference another worksheet, to translate the ID's into names.....

Author

Commented:
The problem with adding anything in the database would be if I changed it in excel then the next time the excel sheet is refreshed you would loose the data entered (this is for a real basic order tracking sheet)
If you had a query that refreshes your workbook with everything that you need when it is opened, you can also put short line of code to force the workbook to save itself as a copy with values only as soon as it refreshes.  That way you have a workbook that always refreshes when it opens and a copy of each refresh. Or a copy of the last refresh, or weekly or daily or however you would want it.....
For example if I open my work book and all the following data is queryed in:
A B C D E
1 2 3 4 5
6 7 8 9 0
At this point it saves it as a new cop with those values on my desktop named (MyQuery07-01-10.xls)....then tomorrow when I open the refresh enable workbook again I might get these values:
A B C D E
5 4 3 2 1
0 9 8 7 6
Now I have 2 work books on my desktop (MyQuery07-01-10.xls and MyQuery07-01-10.xls)
And to get July 3rds I need only open the refresher.
This would be the benefit of pulling all columns from the DB.  You wouldn't lose any data.

Author

Commented:
Here's the problem, Column A is not coming in from the query so when the query is updated the values in Column A do not line up with the data they used to before the query
I understand.  What I'm saying is, the values in column need to come from database along with the rest of the data.....
Column A*
But we can't add them in the database, the values for column A need to be entered via the Excel sheet for tracking.

Thank you for the effort but I was able to figure it out myself.

Solution: Setup external data in Excel as normal and place in cell A1. After data is loaded insert new column and external data table to cover new Column A. This way the values input into the new column stay with their appropriate data next to them upon data change and refresh of the external data (they are somehow linked this way - not sure why but it works)

Cheers