Well I'm not sure what caused the issue, but I had to delete the link and create from scratch to get it to sort of work again.
With version 8 of Monarch Pro, the file would read in the field information which was a basic divison of 2 other fields. The answer would be a long decimal number 5.334455444 as an xample. Excel was set to display this number to 1 decimal point. So it would show 5.3 When it was read into Monarch it would pull the full amount as a number into it's workings. so I would et a result pulled form the table as 5.334455444 to work with.
With Version 10.5, I've managed to recreate the external lookup but it refuses to do anything except a general filed type. meaning it's pulling it like a string. This also means that if face value is 5.3 it pulls 5.3 and disregards the rest of the decimal points.
Short of changing the spreadsheet which is out of my hands and a none option, I'm not sure what I can do. I'm not sure why Monarch Pro is forcing the lookup information to be a type General. Any explanations? :confused:
This sort of thing can be a frustrating exercise at times.
Would it be too much trouble to first save the Excel file as a CSV file and then link to it?
You may find that you need to increase the number of decimals displayed before saving the CSV file, otherwise Excel may save 5.334455 as 5.3 in the CSV file (as my copy of Excel 2010 does), and obviously Monarch will be limited to 5.3.
This solution has worked for many in the past.
I wish I could do that, it's more a file they use for display rather than actual work. If I had my way they'd save it the new format and that would be that. But people hate change. I get the feeling this is the beginning of things changing though. I'm just surprised the newer version of Monarch Pro has changed how it reads the file. Old version was fine new version changes. I understand MS changed their Office products (mumble mumble) so I understand certain changes would be needed to read the new format XLSX files. I didn't think it would or even should affect existing .xls files. And in fact that kind of surprises me. It's almost an MS decision where a new situation arises and Monarch can go one of two was, but instead of giving the user the decision which way to go, it's railroaded a certain way. Perhaps I'm ranting, but I've run into this a few times over the last few years and companies fail to see how it really cuts into their bottom line. The simple fact is it should be the end user who makes the decision. Why not allow me to define the field type of the data used in the external lookup? I still can't figure out why I run into issues using the old format and yetthe new format has no problem, it works fine and I get the result I had before and was expecting. I just wondered if there was a good reasoning for it that anyone knew about because I'm stumped as to why change it. I usually go with the adage that if it isn't broken, why change it, so I'm assuming there's a reason for it. I may never get the answer I'm looking for and might just be stuck with "work around it" Which is a valid answer, just not a good, business buillding one.
Of course, i could be just being overly grumpy too.:)
Just an update to the issue. The fields I was trying to import was a calcuated field in the spreadsheet which were calculated using other fields. for example. C1 was represnting B1 / A1
A solution which worked for me was to import the original fields A1 and B1 and have Monarch do the division work for me. It also allows me to round the results to as many decimal places as I want with Monarch.
Not a perfect fix, but works good enough for me. I figured I'd share and let everyone know.