Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Ruth J
 
Posts: n/a
Default Global way to set absolute links...

Hi,
I am working in one workbook:

Sheet1 is the Master of approx 50 columns by 1400 rows. That data is
refreshed daily from a .txt file. The sheet normally increases in the number
of rows by anywhere from 1 to 100+ rows per day. The columns do not change.

Sheet2 is approx 15 columns linked from the Master plus three additional
columns containing formulas.

Sheet3 is a copy & special paste of 'values and numbers format' of Sheet2.
(this is the page that is used for our data manipulating - sorting, filters,
etc.)

Problem: Sheet2 links sometimes change due to the way the Master is
refreshed. I need to make the links absolute in all 1400 rows. Is there an
easy way to do this other than cell by cell (shiver...smile)?

This is my first time working with links - any help is appreciated!

Thanks,
Ruth J

  #2   Report Post  
Bill Manville
 
Posts: n/a
Default

Ruth J wrote:
Problem: Sheet2 links sometimes change due to the way the Master is
refreshed. I need to make the links absolute in all 1400 rows. Is there an
easy way to do this other than cell by cell


Making them absolute may well not be the solution.
If the method of refresh doesn't result in Sheet1!A1 moving or being deleted
then the following would be give a link from Sheet2!whatever to
Sheet1!whatever:

=OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1)


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #3   Report Post  
cpetta
 
Posts: n/a
Default

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 no be C50:C70
or some other number the target worksheet is still expecting to copy C1:C20.
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 regardless of the cell numbers?

"Bill Manville" wrote:

Ruth J wrote:
Problem: Sheet2 links sometimes change due to the way the Master is
refreshed. I need to make the links absolute in all 1400 rows. Is there an
easy way to do this other than cell by cell


Making them absolute may well not be the solution.
If the method of refresh doesn't result in Sheet1!A1 moving or being deleted
then the following would be give a link from Sheet2!whatever to
Sheet1!whatever:

=OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1)


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
replace absolute references bj Excel Worksheet Functions 0 May 20th 05 07:18 PM
Reoccurring Edit Links Error Spyder Excel Discussion (Misc queries) 6 April 12th 05 02:30 PM
can't update links...can't find links GJR3599 Excel Discussion (Misc queries) 1 April 4th 05 04:56 PM
HELP!!! Missing Links!!! [email protected] New Users to Excel 2 February 16th 05 04:20 PM
Links in formulas change when another user runs a workbook L Mehl Excel Discussion (Misc queries) 2 November 27th 04 10:27 PM


All times are GMT +1. The time now is 01:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"