+ Reply to Thread
Results 1 to 11 of 11

Worksheets & Cell Referencing #REF! Error

  1. #1
    Registered User
    Join Date
    03-17-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    74

    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

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099

    Re: Worksheets & Cell Referencing #REF! Error

    Hi,

    Sounds like the clear button is actually deleting rows rather than the contents of the cells?
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Registered User
    Join Date
    03-17-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    74

    Re: Worksheets & Cell Referencing #REF! Error

    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.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Worksheets & Cell Referencing #REF! Error

    As oldchippy has already inferred you will need to analyse the code associated with the Clear button as it sounds as though it's seemingly deleting & replacing rows from the Blue sheet

    Before doing that to confirm this it the cause... what happens if you use the below

    =MIN(INDEX('BLUE'!1:65536,14,8),INDEX('BLUE'!1:65536,14,12))

    The above is based on the assumption that Blue and Red remain intact once Clear is run.

  5. #5
    Registered User
    Join Date
    03-17-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    74

    Re: Worksheets & Cell Referencing #REF! Error

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

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Worksheets & Cell Referencing #REF! Error

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

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

  7. #7
    Registered User
    Join Date
    03-17-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    74

    Re: Worksheets & Cell Referencing #REF! Error

    Quote Originally Posted by DonkeyOte View Post
    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.

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Worksheets & Cell Referencing #REF! Error

    OK I'm assuming BLUE isn't the real sheet name and the *real* sheet name may contain a space or other characters that necessitate you encase the sheet name within ' ... eg

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

    If the above works your code is definitely removing and replacing the sheet each time meaning the above is the only viable approach unless you alter the code itself which by the sound of it you'll not want to attempt.

    Note: however INDIRECT is Volatile so if you use this formula frequently in a big models performance will deteriorate.

  9. #9
    Registered User
    Join Date
    03-17-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    74

    Re: Worksheets & Cell Referencing #REF! Error

    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

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Worksheets & Cell Referencing #REF! Error

    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.
    Last edited by DonkeyOte; 03-18-2009 at 12:02 PM.

  11. #11
    Registered User
    Join Date
    03-17-2009
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    74

    Re: Worksheets & Cell Referencing #REF! Error

    Quote Originally Posted by DonkeyOte View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1