Changing links on insertion of row

Mudface · Apr 30th, 2002, 10:50 AM

In column A on Sheet1, I have values 1-10 in the first ten rows and on Sheet2 I link to the values in these rows. If I then insert a row, this will not be reflected in the linked data, the links below all shuffle down by one, but they stay in rows 1-10.

Is there a way to do this automatically (I'm not looking for a VB solution, I should be able to do this OK)- ie have the inserted row show up in the linked data?

Thanks in advance.

Ricky Morris · Apr 30th, 2002, 11:57 AM

Keep the linked file open while you make changes to the source file and the changes will be reflected in the linked file.

_________________
It's never too late to learn something new.

Ricky

[ This Message was edited by: Ricky Morris on 2002-04-30 05:59 ]

Mudface · Apr 30th, 2002, 12:06 PM

Thanks, Ricky, but that wouldn't work if you'd inserted a row on the source sheet. I think I've sorted it out by using a query to update the data, rather than via links. Cheers anyway.

Chris Davison · Apr 30th, 2002, 07:17 PM

Evening Sunny Hull,

try :

=INDIRECT("Sheet1!A"&ROW())

in A1 on sheet2 (replacing your actual sheet name!)

this should work if you copy / insert rows

Rainy London,
Chris

Mudface · May 7th, 2002, 10:16 AM

Thanks, Chris, only just seen your reply. The problem's slightly more than that, though. The 'Master' sheet has columns for Project Type, Priority, Engineer etc along with a load of financial info. The guy wanted a separate 'Project' sheet with the first few columns linked to the 'Master' sheet, but none of the financial info. So far so easy, but on the 'Project' sheet he also wanted extra columns for the engineers to record their times against each project, so inserting rows in the 'Master' sheet would then mean all the times were out of kilter on the other sheet.

I told him he would be better starting this all from scratch and using Access, but he didn't have time and I didn't have time to set up his sheet properly (it's a bit of a mess to say the least). So in the end, I just dumped everything on the 'Master' sheet and put in a bit of code to hide/ unhide the financial columns.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 30th, 2002, 10:50 AM	#1
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	In column A on Sheet1, I have values 1-10 in the first ten rows and on Sheet2 I link to the values in these rows. If I then insert a row, this will not be reflected in the linked data, the links below all shuffle down by one, but they stay in rows 1-10. Is there a way to do this automatically (I'm not looking for a VB solution, I should be able to do this OK)- ie have the inserted row show up in the linked data? Thanks in advance.

Apr 30th, 2002, 11:57 AM	#2
Ricky Morris Join Date: Apr 2002 Posts: 363	Keep the linked file open while you make changes to the source file and the changes will be reflected in the linked file. _________________ It's never too late to learn something new. Ricky [ This Message was edited by: Ricky Morris on 2002-04-30 05:59 ]

Apr 30th, 2002, 12:06 PM	#3
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	Thanks, Ricky, but that wouldn't work if you'd inserted a row on the source sheet. I think I've sorted it out by using a query to update the data, rather than via links. Cheers anyway.

Apr 30th, 2002, 07:17 PM	#4
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Evening Sunny Hull, try : =INDIRECT("Sheet1!A"&ROW()) in A1 on sheet2 (replacing your actual sheet name!) this should work if you copy / insert rows Rainy London, Chris

May 7th, 2002, 10:16 AM	#5
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	Thanks, Chris, only just seen your reply. The problem's slightly more than that, though. The 'Master' sheet has columns for Project Type, Priority, Engineer etc along with a load of financial info. The guy wanted a separate 'Project' sheet with the first few columns linked to the 'Master' sheet, but none of the financial info. So far so easy, but on the 'Project' sheet he also wanted extra columns for the engineers to record their times against each project, so inserting rows in the 'Master' sheet would then mean all the times were out of kilter on the other sheet. I told him he would be better starting this all from scratch and using Access, but he didn't have time and I didn't have time to set up his sheet properly (it's a bit of a mess to say the least). So in the end, I just dumped everything on the 'Master' sheet and put in a bit of code to hide/ unhide the financial columns.