If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Copy Paste Special Link Cell Problem
I have a spreadsheet that uses ODBC/MS Query to connect to a database and
pull in part numbers each time the worksheet is opened the file refreshes. The part numbers are put in a column, and the contents from this column are copied to another worksheet using the copy paste special option. Each time the ODBC connects and refreshes the data the cell numbers change and causes the links from source to target not to work. For example if I have 20 part numbers in cells C1:C20 in the source worksheet, which are copied to the target worksheet when I created the orginial links, the next time the file is opened/refreshed the cells in the source worksheet may be C50:C70 or some other number the target worksheet is still expecting to copy C1:C20 from the source worksheet. The part numbers in the target still show the orginial values when I created the orginial links from cells C1:C20 in the source worksheet. How can I fix this so the copy-paste-special links will always copy the current data contained in the source worksheet after the file has refreshed regardless of the cell numbers? |
#2
|
|||
|
|||
I don't understand why the results of a query would move from C1:C20 to
C50:C70 when the query is refreshed. I can accept that the number of rows returned could differ and that individual items might be on different rows. I will assume that is what you meant and that the results table still starts at the same location. Is the copying done by a macro currently? If so please post the code. If not, please record a macro while doing the operation manually and post the recorded code. That way it will be clearer what we are both talking about. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#3
|
|||
|
|||
I refreshed the query again today, and the cells stayed consistent i.e
C29:C30, but the data from source worksheet to target worksheet in the same workbook was still not updating upon opening or refreshing the file. If I manually delete data in say cell C29 or C31 from the source worksheet the target data updates automatically. Why wont the target data update from the source upon opening or refreshing the query? I followed the Excel help menu for recording a macro but did not see any data. I am not using a macro only copy-paste-special link, plus my MS Query to extract information from my database via ODBC which works fine. Can you please help! "Bill Manville" wrote: I don't understand why the results of a query would move from C1:C20 to C50:C70 when the query is refreshed. I can accept that the number of rows returned could differ and that individual items might be on different rows. I will assume that is what you meant and that the results table still starts at the same location. Is the copying done by a macro currently? If so please post the code. If not, please record a macro while doing the operation manually and post the recorded code. That way it will be clearer what we are both talking about. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#4
|
|||
|
|||
Not a problem I have heard of before.
What version of Excel are you using? When the links have not updated after a refresh of the table, what calculation mode are you in (Tools / Options / Calculation)? If you are in manual mode they would not normally update. If you are in automatic mode they should update; does Ctrl+Alt+F9 cause them to update correctly?. What is the formula in one of the cells containing the links? Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#6
|
|||
|
|||
I can only suggest that there is something wrong with your worksheet.
Ctrl+Alt+F9 recalculates all formulas in the workbook so if your links are not refreshed it must be because the calculation chain is not properly formed. Try Edit / Replace / = with = / Replace All on each worksheet That should rebuild the calculation chain If that doesn't work, try rebuilding the workbook from scratch. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#7
|
|||
|
|||
Thanks. I'll give it a try and let you know the results. Also, Thanks for the
quick responce. This is a great user group and more valuable than an online training. Have a great weekend. "Bill Manville" wrote: I can only suggest that there is something wrong with your worksheet. Ctrl+Alt+F9 recalculates all formulas in the workbook so if your links are not refreshed it must be because the calculation chain is not properly formed. Try Edit / Replace / = with = / Replace All on each worksheet That should rebuild the calculation chain If that doesn't work, try rebuilding the workbook from scratch. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
copy combobox - cell link to change automatically | Bojana | Worksheet Functions | 1 | June 8th, 2005 02:35 PM |
"paste special" "paste link" | lssweatt | General Discussion | 2 | March 29th, 2005 08:51 PM |
Link - deactivate being sent to source when click on linked cell? | Dave Peterson | Worksheet Functions | 0 | January 11th, 2005 02:11 AM |
copy and paste special value only on IF function | sq246 | General Discussion | 3 | August 10th, 2004 08:30 PM |
Stubborn Link Data/Value Problem - Help Appreciated! | Jax | Links and Linking | 5 | January 8th, 2004 07:46 AM |