Global way to set absolute links...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ruth J - 14 Jun 2005 14:59 GMT

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

Reply to this Message

Bill Manville - 15 Jun 2005 00:10 GMT

> 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 to this Message

cpetta - 30 Jun 2005 05:42 GMT

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?

> > 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
[quoted text clipped - 10 lines]
> MVP - Microsoft Excel, Oxford, England
> No email replies please - respond to newsgroup

Reply to this Message