Locking Cell Reference

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

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

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

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

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.

Barrie,

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

Santosh

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

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.

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

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

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