Excel: Can't keep rows aligned after refreshing an external data source.

[sgibson]

I have a list of names that I've imported into Excel from Access DB.  That part was easy, however, when the data range expands (or contracts), information does not remain next to the appropriate data.  I've tried to follow the instruction under "copy formulas when an external data range expands", but I guess I'm missing something because it doesn't work.  HELP!

[soybean]

"data range" refers to Excel or Access? 

"information does not remain next to the appropriate data" means what, exactly? 

[s gibson]

"data range" refers to Excel or Access? 

"information does not remain next to the appropriate data" means what, exactly? 

Data range is a list of names imported into an excel file from an Access database. 

Information does not remain next to the appropriate data means that any information put in next to a certain name (ie a date) does not stay next to that name when you refresh data and the data range expands. 

[soybean]

I gather you've set this up so that Excel is supposed to be updated when changes to the database are made?  Is that correct?  I've exported tables from Access to Excel and imported from Excel to Access but I've never developed an application where Excel is antomatically updated with data from Access.  So, I'm not sure how that's done and I may not be able to help with this. 

This forum does not get many Excel questions.  As far as that goes, I believe many other computing forums don't get many Excel questions.  I nornally would not refer someone to another forum but, in this case, I'll mention one focused on Excel: http://www.excelforum.com/.  You might post your question over there.  And, someone else may see the discussion here in ComputerHope and offer a suggestion. 

In the meantime, I may try some testing with the technique you're using.   Please let us know if you find a solution, and what is was. 

[s gibson]

If you go to  Data and Import External Data, you can pull the information (list of names) from the database. 

If you are in the external data range (list of names) you can go to Data and refresh to update the information.

If you go do help and put in About Importing Data, you get "When an external data range expands and additional records are returned, Excel can fill formulas in adjacent columns or within the data range so that they remain next to the appropriate data. "  So this apparently can be done.

If you go to help and put in Fill down Formulas, you'll get the instruction I was trying (and failing) to accomplish.

Thanks for your help

[soybean]

As a test, I imported data into Excel from an Access table.  I closed the Excel file, opened the Access database, updated a few records, exited Access, opened the Excel file and used the Refresh command.  The data in Excel was updated.  So, I see how that works.

Were you importing from an Access table or a query?  Where did the formulas in Excel originate, in Excel or Access? 

[s gibson]

The information in Access is in a query; basically a simple "who do we do this type of service for" query. 

The formula would be an Excel formula.  The instruction I was trying to follow in Excel just said "enter a formula in a cell adajcent to the first row of data" and was not specific as to what kind of formula to enter.  I've tried inserting a formula in all the cells around the first row of data and it hasn't kept the information in the same row. 

Thanks for your help on this.  I didn't mean to keep you up all night on it. 

[soybean]

I'm not able to duplicate your experience.  In other words, I don't have the same problem when using a column just to the right of the external data range for a formula.

I've tried inserting a formula in all the cells around the first row of data ...

Just use the cell immediately to the right of the first data row for the first formula.  Using other cells "around the first row of data" makes no sense.

Once you've entered the formula in that first row, you should be able to fill the other cells in that column by one of several techniques: 1) copy and paste, 2) dragging the data fill handle, or 3) double clicking on the data fill handle (which is a quick and easy way to do it).

The instruction I was trying to follow in Excel just said "enter a formula in a cell adajcent to the first row of data" and was not specific as to what kind of formula to enter.

Ummm, how many "kinds" are there?  Exactly what "kind" of formula are you trying to use?

[s gibson]

I've tried several simple formulas; adding, listing a value from another cell, etc. and I've tried inserting these in a column just to the right (and to the left) of the first row of returned data (and also right and left of the header), then I've been double clicking the fill handle to copy it to all rows and it does fill them. 

The problem I have is that if I have entered information for Mr. Smith on row 6 and then update from the data source and Mr. Smith's name is now on row 7, Mr. Smith's information that I filled in is still on row 6 after someone elses name. 

I've also selected "insert entire rows for new data; clear un-used cells" and "Fill down formulas in columns adjacent to data" from the external data range properties dialog box.