Aligning manually entered data with external data (query)

G

Giunca

New Member
Joined
Feb 29, 2008
Messages
20
(Running XL 2007 on XP)
I have a basic query that returns data from a database to a sheet in my workbook (one record per row). I want to enter data into a cell adjacent to the returned data that will "stick with" the record that it is next to. The problem is that if the external data changes (records added or deleted), upon refresh of the query my text doesn't move with the record it was originally next to.

I have worked through the External Data Properties options (i.e. "Insert Cells...", "Insert Rows...", "Overwrite Existing..."), but none of these options gives me what I need. My text always gets misaligned from the record I originally put it next to. Is there a way to make manually entered data stick next to the records of the underlying external data?

Basic example:

Column A = records returned from the query
Column B = data manually entered into the spreadsheet by me

A * B
-------------------------------------------------
0003 *
0005 *
0010 * comments about record # 0010
0014 *
0019 *
0024 * comments about record # 0024

If record 0004 is added and record 0019 is deleted with the next refresh, I want the comments for records 0010 and 0024 to stay with their respective records. Chart would then look like this:

0003 *
0004 *
0005 *
0010 * comments about record # 0010
0014 *
0024 * comments about record # 0024

Make sense? Any ideas?
Thanks much.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Fazza,
Thank you -- that looks like the same problem, and I think I understand the principle of your solution, but I don't understand the coded part of your solution (is that VBA? I don't recognize it).

I will attempt to figure this out through the standard interface options -- if you can dumb it down for me, that would help.

Thanks much.
 
Upvote 0
Hi.

Currently you have a basic query from a database to a sheet in your workbook. The idea is to add another basic query this time from your workbook. This new query will pull the column A information and the column B information. This query is executed just before you are ready to refresh the query that populates column A. Now you have a table in another database of the column A and column B data. The basic query that originally populates column A becones modified, like the SQL in the other thread, so that instead of just populating column A it also draws data from the new table. So the entire refresh operation is now (1) refresh the new table and then immediately after, (2) refresh the modified original query.

Regards, Fazza
 
Upvote 0
Fazza said:
Hi.

Currently you have a basic query from a database to a sheet in your workbook. The idea is to add another basic query this time from your workbook. This new query will pull the column A information and the column B information. This query is executed just before you are ready to refresh the query that populates column A. Now you have a table in another database of the column A and column B data. The basic query that originally populates column A becones modified, like the SQL in the other thread, so that instead of just populating column A it also draws data from the new table. So the entire refresh operation is now (1) refresh the new table and then immediately after, (2) refresh the modified original query.

Regards, Fazza
Click to expand...

Can someone please explain the concept in the above solution. I understand that one can pull data from an external source but I'm confused about the second query. Like this post states, I have a set of names which I'm getting from a SQL dataset. Next to the column "Names", I have columns which are manually entered in reference to the Name(s). Once I refresh the dataset, I'd like to have the comments line up with the name(s). Any clerification on this issue would help.
 
Upvote 0
Samoht_lee said:
Can someone please explain the concept in the above solution. I understand that one can pull data from an external source but I'm confused about the second query. Like this post states, I have a set of names which I'm getting from a SQL dataset. Next to the column "Names", I have columns which are manually entered in reference to the Name(s). Once I refresh the dataset, I'd like to have the comments line up with the name(s). Any clerification on this issue would help.
Click to expand...

The idea is to create a dynamic query which will read column A data that is in your original spreadsheet and run the query for those in that order. This is what I understand. Need to make it smart enough to add the new Column A data from database at the bottom i guess, I am not sure if this is possible.

I have the same problem and i cannot even send back input to the server directly from the spreadsheet. Need to figure out a way without lookup from inside the sheet. Anyone else with ideas? Microsoft should be reading these threads and improving their product. Or they should read this and hire me. I have so many ideas on what Excel users need.... Need something like take column A create a new column with that data on the right side of refreshable data. Break the link and paste values right before refresh on refresh command. and now you have an identifier but is there a way to sort data to match two different columns with unique identifiers?
 
Last edited:
Upvote 0
ene310 said:
The idea is to create a dynamic query which will read column A data that is in your original spreadsheet and run the query for those in that order. This is what I understand. Need to make it smart enough to add the new Column A data from database at the bottom i guess, I am not sure if this is possible.
Click to expand...

No, that isn't right. The order of the data is not relevant, and adding new data to the bottom is not relevant.

Say there is a database with AnyOtherFields and

ID
1
2
3

This is queried into a worksheet : SELECT ID, AnyOtherFields FROM database

Now for some records information is added to the worksheet in (a) new field/s. This/these field/s "Info" exist/s only on the worksheet. Field order unimportant.

ID Info AnyOtherFields
1 one
2
3 three

Now there is a requirement to refresh the ID (+ whatever other fields come from the database)

First, query the worksheet data and keep the critical identifying code & fields added in the worksheet : SELECT ID, Info FROM [worksheetname$]

Store that on a worksheet. Maybe hidden.

The original query from the database is modified to now also grab the Info from this (maybe hidden) new sheet.

So that query changes,
from original : SELECT ID, AnyOtherFields FROM database
to become : SELECT D.ID, W.Info, D.AnyOtherFields FROM database D LEFT OUTER JOIN worksheet W ON D.ID = W.ID

Any time a refresh is wanted it is a two step process,
1. refresh the worksheet that has the wanted Info field/s
2. refresh the modified original query to get data from both the main database & the wanted Info field/s from the just refreshed query 1

This is explained in several threads but doesn't seem to be understood. Some slight variation on the idea might be needed for specific requirements.

If the idea of the queries isn't easy, do something similar via VBA. Such as store the data from the worksheet either in another worksheet, or an array, or a recordset, or whatever. Refresh the data from the database, then loop through the copy of the data before the refresh - stored in the worksheet/array/recordset/whatever - and re-populate the Info field/s. The advantage of the first described approach is it is just two queries to refresh instead of one, and there is no need to loop. I guess instead of a loop, one could use VBA and INDEX/MATCH formulas. Or VLOOKUP. Or whatever.

hope this helps
 
Upvote 0
Fazza said:
No, that isn't right. The order of the data is not relevant, and adding new data to the bottom is not relevant.

Say there is a database with AnyOtherFields and

ID
1
2
3

This is queried into a worksheet : SELECT ID, AnyOtherFields FROM database

Now for some records information is added to the worksheet in (a) new field/s. This/these field/s "Info" exist/s only on the worksheet. Field order unimportant.

ID Info AnyOtherFields
1 one
2
3 three

Now there is a requirement to refresh the ID (+ whatever other fields come from the database)

First, query the worksheet data and keep the critical identifying code & fields added in the worksheet : SELECT ID, Info FROM [worksheetname$]

Store that on a worksheet. Maybe hidden.

The original query from the database is modified to now also grab the Info from this (maybe hidden) new sheet.

So that query changes,
from original : SELECT ID, AnyOtherFields FROM database
to become : SELECT D.ID, W.Info, D.AnyOtherFields FROM database D LEFT OUTER JOIN worksheet W ON D.ID = W.ID

Any time a refresh is wanted it is a two step process,
1. refresh the worksheet that has the wanted Info field/s
2. refresh the modified original query to get data from both the main database & the wanted Info field/s from the just refreshed query 1

This is explained in several threads but doesn't seem to be understood. Some slight variation on the idea might be needed for specific requirements.

If the idea of the queries isn't easy, do something similar via VBA. Such as store the data from the worksheet either in another worksheet, or an array, or a recordset, or whatever. Refresh the data from the database, then loop through the copy of the data before the refresh - stored in the worksheet/array/recordset/whatever - and re-populate the Info field/s. The advantage of the first described approach is it is just two queries to refresh instead of one, and there is no need to loop. I guess instead of a loop, one could use VBA and INDEX/MATCH formulas. Or VLOOKUP. Or whatever.

hope this helps
Click to expand...




Hi So Basically, what is happening is that your suggesting a "loop" by opening up ms database and throwing it back into the original worksheet.

Right?
 
Upvote 0
It isn't a loop in my mind.

The text you quote describes it as best I can. And as I wrote, it doesn't seem to be understood. I guess it is my explanation.

Not a loop, but a two step refresh.

Please can you explain step by step how you understand it? And explain the loop part.

Or if you don't understand it, please explain steps to provide the functionality. (Even if not detailed, just generally.)

regards
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Insert new row with incremental formula
Replies
8
Views
587
Sheik
S
M
  • Solved
VBA Help - Duplicate & Unwanted Data Removal
Replies
6
Views
931
MoonDancer
M
M
  • Solved
VBA to swap cell values, but enter the swapped value in cell below.
2
Replies
10
Views
694
myall_blues
myall_blues
J
  • Solved
Button to move data from current row to a pdf
2
Replies
17
Views
1K
Candyman8019
C
N
  • Solved
Loop to get emails from Outlook 365 has broken, and I cant work out why !
Replies
3
Views
785
netrixuser
N

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top