Ghost formula blocking the screen

Alex Saint · Mar 26, 2016

Hello
I am a basic excel user. I use it frequently for budgeting mainly but nothing especially complicated. Basic adding up and taking away, project planning, occasionally I will import an excel spreadsheet as a form into word -- but really nothing complex. Am sure i could use it better, but don't really need to.
Recently 'ghost' formula/links (eg grey cells annotated R2, C3 etc ) have started overlaying themselves across the screen. They don't relate in any way to the workbook I am working on, and aren't 'live'. It is as if a piece of tracing paper has been laid of the top and it is making Excel nearly impossible to use as the screen is obstructed.
I don't know how to identify the spreadsheet that it is pulling from - or how to find out if if I have at some point 'saved' some data in a clipfile maybe that is now misbehaving.
A google search pulled up a thread about this some years back (2012) but am afraid I couldn't understand the answer, which is why I am asking again.
The answer given was "you unprotect a worksheet by using the object model such as worksheets ("sheet2"). Unprotect. If the unprotected worksheet is not the active worksheet, contents from the unprotected worksheet become visible on the current worksheet. This issue continues until there is an action that updates Excel."
That sounds like my problem. But I really need very simple step by step instructions re how to resolve or pointing in the direction of hands-on help and intervention.
Many thanks
Alex - Manchester UK

dave3009 · Mar 26, 2016

Hi Alex, welcome to the forum.

That sounds really odd, are you able to take a screenshot and post it up. The issue could be as simple as a basic setting or something far most complex, it would probably help people if they could visualise it.

Thanks

Dave

B___P · Mar 26, 2016

Put the code in a module then switch to the problematic sheet and run the macro.

Code:

Sub ShowOutsideRef()
x = 1
Set myrange = Application.InputBox(prompt:="select a range to verify", Type:=8)
For Each c In myrange
If InStr(1, c.Formula, "!", vbTextCompare) Then
c.Interior.Color = vbYellow
Sheets(2).Cells(x, 1).Value = c.Address & "-->" & c.Formula
x = x + 1 '
End If
Next
End Sub

Cells with references outside the sheet will be in yellow and their formulas will be listed in the second sheet in workbook.

Bye

Alex Saint · Mar 26, 2016

Hi Dave
Am struggling to attach a screenshot - insert image seems to want a url?
But yes it is odd!

Alex Saint · Mar 26, 2016

Hi B-P
You've lost me there ....'module' 'problematic sheet' and 'run macro'. Am afraid I don't know where to start.
But I will take a note of this code in case I find someone who can physically do it for me
Thankyou so much
Alex

dave3009 · Mar 26, 2016

Hi Alex

You can use an image upload service like tinypic or imgur to upload your screenshot, then you can use the created URL to post your picture.

HTH

Dave

Worldsmith · Mar 26, 2016

In order to post a picture with a URL, first you need to upload your image to a sharing site such as photobucket.com or imgur.com (I use one of these two sites, and I'm not necessarily advocating for either. Just trying to give examples).
First you'd need to create an account, then upload your photos to that site, then you can share the photo on these by the URL.

You can get the URL of an image multiple ways, but the surefire way to get it is to right-click on it, select Properties and the URL will display. If the "Properties" option is not in the menu, you can click "Copy Image Address"

Alex Saint · Mar 26, 2016

Thankyou so much - image link is in the box below

Alex

Alex Saint · Mar 26, 2016

thanks worldsmith - done I think

Alex Saint · Mar 26, 2016

or here?
Imgur: The most awesome images on the Internet

Ghost formula blocking the screen

Alex Saint

New Member

Excel Facts

dave3009

Well-known Member

B___P

Active Member

Alex Saint

New Member

Alex Saint

New Member

dave3009

Well-known Member

Worldsmith

New Member

Alex Saint

New Member

Alex Saint

New Member

Alex Saint

New Member

Similar threads

Forum statistics

Share this page

Ghost formula blocking the screen

New Member

Excel Facts

Well-known Member

Active Member

New Member

New Member

Well-known Member

New Member

New Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock