MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Locking Cell Reference


Posted by Santosh on May 04, 2001 8:41 AM

I am trying to link two worksheets within the same file. When editing the original worksheet, I am getting errors on the linked worksheet.

For example, Worksheet 1 (WS1) contains the data and Worksheet 2 (WS2) is linked to WS1. Cell A1 in WS2 contains the same data as cell A1 is WS1 because of referencing. When I make changes in WS1, it is necessary for me to cut and paste rows, along with deleting and inserting rows. When I delete a row is WS1, it gives me an error is WS2 because the reference cell(s) is gone.

My goal is to have WS2 always referencing the same cell location in WS1. I want cell A1 in WS2 to always look at cell A1 in WS1. If I delete the first row in WS1, then I want WS2 to look at the new cell A1 in WS1 (which was originally cell A2), not give me an error message. The reference in WS2 should still be to WS1 A1, not WS2 A2.

Any help you could give me would be greatly appreciated.


Posted by Kevin James on May 04, 2001 9:05 AM

Hi Santosh,

Others may disagree with me, but it seems you'd be better served by having an external link to a different workbook. That way, when you perform editing in the modifyable workbook, it doesn't affect the formulas in the view-only workbook.

Kevin

Posted by Santosh on May 04, 2001 9:19 AM

Kevin,

Thanks for the suggestion. As long as the formulas do not change, this will solve my problem. I don't mind keeping two workbooks.

Thanks for your help.

Santosh

Posted by Santosh on May 04, 2001 9:30 AM

Kevin,

I just tried what you suggested and that didn't work. Here is the reference I used in Book 2 (Book 1 contains the data):

=[Book1.xls]Sheet1!$A$1

When I delete cell A1 in Book 1, cell A1 in Book 2 now reads: =[Book1.xls]Sheet1!#REF!

This is a similar error message I received when I used different worksheets in the same workbook.

Any other suggestions?

Santosh

Posted by Barrie Davidson on May 04, 2001 10:00 AM

Do you have Book 2 open when you are deleting the row in Book 1? If you do, this will always happen. Try closing Book 2 before you delete the row in Book 1. After you delete the row, re-open Book 2 and the reference should still be there.

Barrie

Posted by Kevin James on May 04, 2001 10:04 AM

ya, what he said.

I was also going to say the same thing.

The book with the links must be closed before editing the other book.

Posted by Santosh on May 04, 2001 10:12 AM

Barrie,

That works. I appreciate you help in getting this problem resolved quickly. I never would have found this solution on my own.

Santosh

Posted by Santosh on May 04, 2001 12:18 PM

Follow-Up Question - Copying Format of Original Cell

Kevin or Barrie,

Do you know if it's possible to automatically copy the format (i.e. font, style, etc.) from the original cell into the linked cell? It would be helpful to have the linked cell look identical to the original cell. When I modify the original cell, the linked cell should change automatically.

Thanks again for both of your help.

Santosh

Posted by Kevin James on May 04, 2001 1:54 PM

Re: Follow-Up Question - Copying Format of Original Cell

Hi Santosh:

Boy, my feelings are all hurt. Here you thank Barrie for his input, but i don't even get honorable mention. (My therapist is gonna love this.) Just kidding.

I appreciate what you are asking. I've had the same problem before. Frankly, the only solution I've found is to use the format painter from the original and "paint" it to the new sheet.

Have a great weekend.

Posted by Dave Hawley on May 04, 2001 2:23 PM

Re: Follow-Up Question - Copying Format of Original Cell


Hi Santosh and Kev ! :o)

I haven't read all the thread here but, this simple code will transfer the formats of cell A1 to Cell B1 on recalculation. All you would need is a simple =A1 in B1.

To put this code in right click on your sheet name tab and select "View Code" and paste in this code:

Private Sub Worksheet_Calculate()
'Wriiten by OzGrid Business Applications
'www.ozgrid.com

''''''''''''''''''''''''''''''''''''''''''
'Hides Rows in that have and Error value in Column A

Range("A1").Copy
Range("A2").PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub


Push Alt+Q to return to Excel and Save.


Dave


OzGrid Business Applications

Posted by Santosh on May 07, 2001 7:30 AM

Re: Follow-Up Question - Copying Format of Original Cell

Kevin,

You got the ball rolling with your idea. I didn't mean to not acknowledge your help in this problem. I was waiting to thank you after you next comment. I had a feeling you would not leaving me hanging.

A special thanks to you for enhancing my knowledge and understanding of Excel. I'm sure I'll have other questions so stay tuned.

Santosh

Posted by Santosh on May 07, 2001 8:13 AM

Re: Follow-Up Question - Copying Format of Original Cell

Dave,

The code you gave me worked with a minor modification (I changed A2 to B1). This worked great. But do you know how to do this with links to other workbooks where the format would be copied from the external workbook? I tried to modify the code to fit my need but I kept getting errors.

Thanks for your help.

Santosh