help! links between cells on 2 worksheets(even when removed and replaced with same name)

**aerialmotionpictures** · 05-22-2012, 10:47 AM

So I am in need of urgent help! I am very familiar with Excel, but this is a conundrum.

So I have worksheet 1 and worksheet 2. my worksheet 2 has a nice report layout which has a formula like so... ='worksheet 1'!A1. taking information from Raw data in worksheet one. easy enough.

I want to be able to replace worksheet one with a different work sheet. Now when i replace worksheet 1 even if i keep the same name 'worksheet 1' the formula ='worksheet 1'!A1 no longer works.

This might be something really simple. I really hope so.

Thank you in advance!

Regards

Nick.

**Søren Larsen** · 05-22-2012, 11:31 AM

When you change the name of a worksheet, then formulas in cells refering to this worksheet will automatically change, so as they continue to refer to the same worksheet. If you delete a worksheet, you will get a #REF! error.

So if you want to change the worksheet that your report is referring to, you can either incorporate =INDIRECT() in all your formulas in sheet2, or use Find/replace to change the worksheet-name part of your references in sheet2.

Alternatively you can have a macro changing references when you change sheetname...

**aerialmotionpictures** · 05-22-2012, 02:00 PM

Thanks very much, just what i was looking for. Simple Answer!

help! links between cells on 2 worksheets(even when removed and replaced with same name)

LinkBack

Thread Tools

Rate This Thread

Display

help! links between cells on 2 worksheets(even when removed and replaced with same name)

Re: help! links between cells on 2 worksheets(even when removed and replaced with same nam

Re: help! links between cells on 2 worksheets(even when removed and replaced with same nam

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions