Excel database link keep rows aligned
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)
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)
MWGainesJR
I'm not quite understanding what you're wanting. Could you provide a sample workbook with static values? Maybe looking at it would help.
ASKER
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
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.....
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.....
ASKER
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)....t
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.
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)....t
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.
ASKER
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*
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.