+ Post New Thread
Results 1 to 5 of 5
Office Software Thread, [Excel/SIMS] Interpreting Reg Groups as Scientific Formulae in Technical; Before I start writing a macro to correct this (which will be a total PITA as it'll have to be ...
  1. #1

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,447
    Thank Post
    1,438
    Thanked 1,168 Times in 797 Posts
    Rep Power
    707

    [Excel/SIMS] Interpreting Reg Groups as Scientific Formulae

    Before I start writing a macro to correct this (which will be a total PITA as it'll have to be applied to every report in the system!), does anybody know how to stop Excel automatically formatting cells?

    The problem I'm having is that some of our Reg groups contain the letter 'E' followed by a single number (e.g. 10E1, 9E2, 8E1 etc.). When you run a report from SIMS and set the output to Excel, Excel sees this and interprets it as a scientific forula so 10E1 becomes 100, 9E2 becomes 900 etc.

    Any ideas?

  2. #2

    Join Date
    Oct 2010
    Posts
    37
    Thank Post
    4
    Thanked 1 Time in 1 Post
    Rep Power
    10
    Sorry LosOjos - I can't offer any help here. I spent the first 10 years of my school career dealing with this. (We are a 3 site school and from when the three sites amalgamated in the 80's the tutor groups were named 10W1 - meaning on the west site; 10E1 - meaning east site and 10C1 meaning centre site. When some wag decided we would start a house system I wrung my hands and worried for ages about having to make wholescale changes to Sims. When I had made the changes, I then realised I had lost this problem. Perhaps you could make this suggestion!!

  3. #3

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,447
    Thank Post
    1,438
    Thanked 1,168 Times in 797 Posts
    Rep Power
    707
    It's so irritating! It stems from the way SIMS generates the CSV I believe. If each field (other than numeric fields) were encased in quotes, I think Excel would interpret them as text rather than formulae...

    But nout I can do about that! So looks like I'm just going to have to live with it! Thanks for replying Anjelica, it's nice at least to know I'm not the only one!

  4. #4

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,403
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    Have you tried creating an Excel template with the column in question formatted as text?

  5. #5

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,447
    Thank Post
    1,438
    Thanked 1,168 Times in 797 Posts
    Rep Power
    707
    Quote Originally Posted by CESIL View Post
    Have you tried creating an Excel template with the column in question formatted as text?
    When SIMS runs reports in to Excel, the template you create isn't the same workbook that gets used for the result - that just imports the data from CSV, manipulates it and puts it in to a new sheet.

    At the moment, my only way around it is to parse the column and "hard code" the values (just precede them with ', hard code sounded more impressive lol)

SHARE:
+ Post New Thread

Similar Threads

  1. [SIMS] Exporting to Fronter - missing reg groups
    By adamf in forum MIS Systems
    Replies: 3
    Last Post: 11th October 2010, 04:00 PM
  2. SIMS.net tabulated reg group report
    By saundersmatt in forum MIS Systems
    Replies: 21
    Last Post: 27th March 2010, 07:27 PM
  3. How do you insert a formulae on open office
    By stu in forum How do you do....it?
    Replies: 4
    Last Post: 1st April 2008, 11:47 AM
  4. Replies: 19
    Last Post: 18th March 2008, 03:19 PM

Thread Information

Users Browsing this Thread

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

Posting Permissions

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