Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Nov 2008
    Location
    England, Yate, Bristol
    Posts
    51
    Thanks
    4
    Thanked 0 Times in 0 Posts

    #N/A when sheet is protected

    Please, please, please help. I must have spent 20 hours working on this with no resolution.

    I am creating a master form to be held in many schools.

    The user inputs data, which is held on a hidden sheet in 20 cells in 1 row.

    When the user completes the form and saves, the form saves as a name made up of different cell data:

    '=IF(F8="YES","ACC"&AccForm!J6&"-"&AccForm!L7&"-"&TEXT(AccForm!L6,"dd-mm-yyyy")&"-A","ACC"&AccForm!J6&"-"&AccForm!L7&"-"&TEXT(AccForm!L6,"dd-mm-yyyy"))

    Which results in something like this: ACC1a-2341-19-05-2014-A

    Trouble is sometimes it works and sometimes returns #N/A

    Its all a bit complicated so I've attached 2 workbooks. 1-Master & 2-Database to collect the results. Password will be PDA

    Thank you.

    Peter

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you walk us through how to get the #N/A error and where that formula is...

    Steve

  3. #3
    Star Lounger
    Join Date
    Nov 2008
    Location
    England, Yate, Bristol
    Posts
    51
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks for picking this up.

    1 - complete the form and press the Save and Close button
    2 - Press the Import button on the Excel Database. 2 of the fields returned have a #N/A

    On the ReportForm on the hidden sheet 'Calculations', the School Reference is grabbed with the DfE number.
    The idea is that the ReportForm is Saved As... with the School Reference number as the file title leaving the master ready for more use.
    The ReportForm is protected in 2 ways:
    1 - The AccForm Sheet is protected with editable text entry cells
    2 - The ReportForm master file is protected through the File Properties in Windows Explorer

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    It seems your attachments have been removed, could you reattach them?

    Steve

  5. #5
    Star Lounger
    Join Date
    Nov 2008
    Location
    England, Yate, Bristol
    Posts
    51
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I have found the problem.

    I am using Excel 2013

    I have a lookup to lookup a reference number for a selected school, e.g. 2008. '=INDEX(SchoolList,MATCH(A24,SchoolNames,0),1)

    Excel is converting the number from General to a date and the reference number is used in 2 fields.

    Every time a school is selected that has a reference number that looks like a year it throws up an error, although there is no indication of this Excel assumption in the sheet.

    Resolved by changing all the fields that reference that number to a number field.

    Thanks
    Peter

Posting Permissions

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