Worksheets & Cell Referencing #REF! Error

Hello, I could really do with some help here

I have 3 worksheets:

Lets call them RED, BLUE AND GREEN

Essentially GREEN pulls Values from BLUE and BLUE pulls Values from RED. Red being the base worksheet from which everything is calculated.

In my GREEN workbook I have the following code:
=MIN('BLUE'!H14,'BLUE'!L14)

And this will work fine.....Until I press a clear Worksheet button that has been implemented on the RED worksheet (button was not implemented by me and I do not wish to edit anything with respect to that button)

When RED is reset the #REF! Error will appear in GREEN. This is fine because it cant find any values From BLUE. However when I do input new values into RED, which in turn updates BLUE which is where GREEN in theory should then be able to pull the results from.... But it just stays with a REF! error.

Is there anyway i can permanently set a CELL to have the following code =MIN('BLUE'!H14,'BLUE'!L14), no matter what happens to other cells?

This is REAllllllllllllly Annoying me!! arf

Well the main RED worksheet pulls lots of data in from a website. So basically I can Clear worksheet, Type some numbers in and hit get data (This is also another button) and it will fill the RED worksheet with data from a website.

The References in BLUE remains intact even when the RED worksheet is cleared. And the link between RED and BLUE are fine, Its just the links between Blue and Green that go haywire.

That works until I click reset worksheets. I guess I have to examine some code now

Originally Posted by DonkeyOte

That is interesting... I wonder if the code deletes & reinserts the Blue sheet... what happens with:

=MIN(INDIRECT("Blue!H14"),INDIRECT("Blue!L14"))

I cant get this to work I get a #REF! error immediately.

I have looked at the code (this is where I am completely out of my depth) It looks like there is a Template of BLUE, I guess that somewhere in the code it is re-inserting a brand new BLUe template after it has reset the red. So that the referencing works. This is a merely a guess I really got no idea and I wish i could paste the code, but i am not allowed.

I think i found the code that does the work, BLUE=Valuation Results btw

Sub Valuation_Results_Worksheet_copy()
On Error Resume Next
Dim svAlerts As Boolean
svAlerts = Application.DisplayAlerts
Application.DisplayAlerts = False
Sheets("Valuation Results").Delete
Application.DisplayAlerts = svAlerts
Sheets("Valuation Results Template").Copy Before:=Sheets("Service Data")
If LCase(Left(ActiveSheet.name, Len("Valuation Results Template"))) _
= "valuation results template" Then ActiveSheet.name = "Valuation Results"
ActiveSheet.Visible = True
End Sub

I really wanted to avoid going down this route, but If i have to i will.....expect to see more of me guys:P

Originally Posted by DonkeyOte

OK, did you test the INDIRECT as shown in my last post wherein you encase the sheet name within ' ... ie:

=MIN(INDIRECT("'Valuation Results'!H14"),INDIRECT("'Valuation Results'!L14"))

I suspect based on what we know presently that the INDIRECT method above is likely to your best bet -- without knowing what your file is doing exactly it's hard to comment as to whether a more suitable route is open to you...
ie one approach could be to alter all formulae on Green sheet to Text prior to deletion of Blue sheet (via the code) and then coercing back to formulae again post reinstatement of Blue sheet... but it may be a faff.

I did...and it didnt work - but it does now (i probably did something wrong). Thats excellent for the time being I can proceed. tyvm.