unwanted links come when new rows are inserted
 

If anyone can assist, I'd be most grateful.
Firstly, Extend Formats & Formulas to Additional Rows is checked in Oprions
under the Tools menu.
BUT, as well as extending formats & formulas (which is really useful and I
want to keep this facility), links to info sources from the row below are
also collected and implanted into the newly inserted row (I use the Insert
drop down menu), which isn't useful and will cause major problems with
spreadsheet results. Is there a different box which needs to be checked or
some other measure which will sort thisout for me?

unwanted links come when new rows are inserted
 

Twishlist wrote:
If anyone can assist, I'd be most grateful.
Firstly, Extend Formats & Formulas to Additional Rows is checked in Oprions
under the Tools menu.
BUT, as well as extending formats & formulas (which is really useful and I
want to keep this facility), links to info sources from the row below are
also collected and implanted into the newly inserted row (I use the Insert
drop down menu), which isn't useful and will cause major problems with
spreadsheet results. Is there a different box which needs to be checked or
some other measure which will sort thisout for me?


It would probably help if you shared with us the formulas that are being
copied inappropriately. I have difficulty in interpreting what exactly you
mean by
links to info sources from the row below are
also collected and implanted into the newly inserted row


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

unwanted links come when new rows are inserted
 

Thanks, Bob
The spread sheet has links in varius cells across each row which collect
data from other spreadsheets. They are all simple links, such as
='M:\Current Project Sites\System Upgrades\2006\Contract
Summary.xls]Upgrade.'!$C$5
When I insert a new row, the links from the row below automatically appear
in it. I believe it is associated with the Extend Formats & Formulas to
Additional Rows checkbox, as described in my first post.

"Bill Manville" wrote:

Twishlist wrote:
If anyone can assist, I'd be most grateful.
Firstly, Extend Formats & Formulas to Additional Rows is checked in Oprions
under the Tools menu.
BUT, as well as extending formats & formulas (which is really useful and I
want to keep this facility), links to info sources from the row below are
also collected and implanted into the newly inserted row (I use the Insert
drop down menu), which isn't useful and will cause major problems with
spreadsheet results. Is there a different box which needs to be checked or
some other measure which will sort thisout for me?


It would probably help if you shared with us the formulas that are being
copied inappropriately. I have difficulty in interpreting what exactly you
mean by
links to info sources from the row below are
also collected and implanted into the newly inserted row


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

unwanted links come when new rows are inserted
 

Twishlist wrote:
When I insert a new row, the links from the row below automatically appear
in it


I am surprised that formulas are copied from the row below; I have only ever
experienced them being copied from the row above.

If you switch off the option to extend data range formats and formulas does
the undesired behaviour cease? If so, then I guess you have 2 choices:
- turn off the option and copy the formats and formulas manually
- leave the option on and remember to clear the cells with the undesired
formulas.

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

unwanted links come when new rows are inserted
 

Apologies, Bill, links do repeat from row above new row. I have unchecked
the 'extend data range formats and formulas' box, and a new row inserted
still has the links from the one above. Any ideas?

"Bill Manville" wrote:

Twishlist wrote:
When I insert a new row, the links from the row below automatically appear
in it


I am surprised that formulas are copied from the row below; I have only ever
experienced them being copied from the row above.

If you switch off the option to extend data range formats and formulas does
the undesired behaviour cease? If so, then I guess you have 2 choices:
- turn off the option and copy the formats and formulas manually
- leave the option on and remember to clear the cells with the undesired
formulas.

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

unwanted links come when new rows are inserted
 

Sorry, I have no ideas.
It would help to know which version of Excel you are using and how you
are causing the new row to be inserted.
Are there any macros in the file?

If you like, email the file to me at Bill underscore Manville at
Compuserve dot com, and tell me where / how to insert a row to see the
effect.

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

unwanted links come when new rows are inserted
 

Thanks, Bill

Winging its way to you know, entitled Rows/links Issue. Look forward to
your response.

Regards
Patricia

"Bill Manville" wrote:

Sorry, I have no ideas.
It would help to know which version of Excel you are using and how you
are causing the new row to be inserted.
Are there any macros in the file?

If you like, email the file to me at Bill underscore Manville at
Compuserve dot com, and tell me where / how to insert a row to see the
effect.

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

unwanted links come when new rows are inserted
 

For the record, the rows concerned were in an Excel 2003 List.

Copying the formulas down happens in Lists

I suggested either converting the List back to a Range or using a
Worksheet_Change macro to detect a row being added to the list (or the
first cell in a row being cleared) and having the macro clear the
formulas that were not desired.

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

unwanted links come when new rows are inserted
 

I was unaware that the rows being in a List made a difference, thanks for
clarifying that. Although lists make things easy, I'll convert to range as a
macro is a tad beyond my capability at this point. Thanks for your assistance.

"Bill Manville" wrote:

For the record, the rows concerned were in an Excel 2003 List.

Copying the formulas down happens in Lists

I suggested either converting the List back to a Range or using a
Worksheet_Change macro to detect a row being added to the list (or the
first cell in a row being cleared) and having the macro clear the
formulas that were not desired.

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