Page 1 of 1
[Solved] Calc Err: 502
Posted: Mon Aug 29, 2011 5:29 pm
by ralgith
Ok, I know nothing, I repeat nothing about programming Spreadsheets. The sheet in question is something I downloaded.
The original sheets in Office 2k7 format can be found here:
http://www.classicbattletech.com/forums ... topic=1219
Using that and a copy I converted to Office 2k3 format I get the 502 error on several functions using Indrect to reference other sheets. The original author of the sheets posted this reply when I brought it up to him:
http://www.classicbattletech.com/forums ... #msg228528
Now, I'd really like to get this working if I could. Thanks for any help ahead of time!
OO.o version in Sig.
Files can be found here (Contains both sheets that are having issues, both of them in Office 2k7 and Office 2k4 formats - I converted the 2k7 to 2k3 using the Official Converter from MS):
Download (folder link, has 4 files in it)
Re: Calc Err: 502
Posted: Mon Aug 29, 2011 6:49 pm
by ralgith
I'm sorry, the Random <Mech/Vee> Generator sheet in the book is the one with the error. Cell H6. And I can't work with the PDFs to use formulas/macros
Its the 3rd from last sheet in the book for the Mechs, probably same for Vees. It occurs on both spreadsheets.
I should clarify the "converted" issue:
I used the convertor in order to try and use them in MS Office 2k3 under Crossover, this doesn't work for me because when running under Crossover MS Office Excel 2k3 cannot load the Add-Ins that are needed in order for the sheets to work. Neither the original nor the converted work in OO, both give the 502 error on that Cell.
The sheet following the one I'm wanting fixed may also give errors, but I really don't care about that sheet.
Re: Calc Err: 502
Posted: Mon Aug 29, 2011 7:03 pm
by gerard24
In H6, you have =VLOOKUP(G5;INDIRECT(F5);3)
F5 contains "MerPerGen30283050Assault". And if I hit Ctrl+F3, i don't see this name...
Re: Calc Err: 502
Posted: Mon Aug 29, 2011 7:10 pm
by ralgith
Once again, this does work in MS Office, it uses the final Sheet (Matrix) to figure out which sheet (based on F5) to perform the lookup from. But I'm not sure how this all works, since I don't program the things myself. BigDuke66 from the link in 1st post does.
I've hit CTRL-F3 myself, and I don't see a way to take that and pick one of the named sheets (In This Case it would be the sheet "Mercenary & Periphery 3028-3050" and lookup on the Assault table section from it using the number in G5 to match the range in that table's column's A&B
So does something need defined in the Matrix sheet for these codes then? And if so, how does it work in Excel w/out them?!
Re: Calc Err: 502
Posted: Mon Aug 29, 2011 10:03 pm
by Villeroy
=VLOOKUP(G5;INDIRECT(F5);3) looks up G5 in the range that is specified by name in cell F5 and returns the value from column 3 at the last position where column 1 is smaller than or equal to G5.
This works in Calc exactly as it works in Excel.
F1-Help on error codes wrote:502
Invalid argument
Function argument is not valid, for example, a negative number for the root function.
I put this in A1:C3
Select A1:C3 and type Named_Range into the name box left of the formula bar. Now this range is named "Named_Range"
I put the text "Named_Range" into F5
I put 1.5 in G5
I put your formula anywhere and it returns y. Wonderful.
Now I hit Ctrl+F3 and resize Named_Range to A1:B3 and since there is no 3rd column to return from, I get the 502 error.
Again, this works in Calc and Excel likewise:
http://www.techonthenet.com/excel/formulas/vlookup.php
techonthenet.com/excel/formulas/vlookup.php wrote:Note:
If index_number is less than 1, the VLookup function will return #VALUE!.
If index_number is greater than the number of columns in table_array, the VLookup function will return #REF!.
Excel returns #REF! whereas Calc returns a more specific numbered error.
Re: Calc Err: 502
Posted: Mon Aug 29, 2011 10:34 pm
by ralgith
You're speaking greek. I'll reiterate: I didn't author these sheets. I don't know how to do this stuff. It does work in Excel 2k3 and 2k7 as advertised when you go to Tools->Add-Ins and check all the ones needed for this. You only get the #REF! error when you don't have the Add-Ins installed. Sometimes you'll get a #NAME error also when you don't have the proper Add-Ins. So, obviously something is different between the two.
So, telling me "it works" if you put it into some test sheet instead of looking at the huge book I've linked to that has several sheets and is doing these lookups across sheets using a special "Matrix" sheet and stuff... does not help me. It doesn't work for OO.o, and I'm asking someone to figure out how its supposed to be so that it does work. Not try to point me in the right direction when I have no clue to start with on how this works, and I'm not interested in learning either.
Re: Calc Err: 502
Posted: Mon Aug 29, 2011 10:41 pm
by Villeroy
I don't tell you "it works". I describe precisely how it is supposed to work and how I make it work in Calc or any other spreadsheet program such as Gnumeric and Excel.
According to my analysis there is at least one reason why this formula may throw error #502. According to the linked documentation on VLOOKUP in Excel, these sheets can not work in Excel nor Calc for the same reason.
If you have no clue, then you've got to use what works for you.
Re: Calc Err: 502
Posted: Mon Aug 29, 2011 10:43 pm
by TheGurkha
ralgith wrote:You're speaking greek. I'll reiterate: I didn't author these sheets. I don't know how to do this stuff. It does work in Excel 2k3 and 2k7 as advertised when you go to Tools->Add-Ins and check all the ones needed for this. You only get the #REF! error when you don't have the Add-Ins installed. Sometimes you'll get a #NAME error also when you don't have the proper Add-Ins. So, obviously something is different between the two.
So, telling me "it works" if you put it into some test sheet instead of looking at the huge book I've linked to that has several sheets and is doing these lookups across sheets using a special "Matrix" sheet and stuff... does not help me. It doesn't work for OO.o, and I'm asking someone to figure out how its supposed to be so that it does work. Not try to point me in the right direction when I have no clue to start with on how this works, and I'm not interested in learning either.
Try to remember that this isn't tech support. This is a forum peopled by volunteers - nothing official. You'll get nowhere with spreadsheets unless you are willing to learn and put some time into understanding them.
Re: Calc Err: 502
Posted: Mon Aug 29, 2011 11:03 pm
by Villeroy
Finally I got the document "3028-3050 Faction Assignment & Rarity Tables 8.06 MECHS.xls" (had some strange connection problems with mediafire) and opened the file in Gnumeric (which is more Excel compatible in some respect).
As a matter of fact there does not exist any range named "MerPerGen30283050Assault", so that VLOOKUP can not work. Just another case where people waste time with gaming rather than skills.
Re: Calc Err: 502
Posted: Tue Aug 30, 2011 12:25 am
by ralgith
Which doesn't explain why so many people use this perfectly fine in Excel, including the author and myself. I just want it working under Linux so I can take my laptop to gaming sessions with me and randomly generate forces. In my case it isn't wasting time gaming rather than skills... I write PHP programs for a living and do quite well with it. I simply have no interest in learning how to do spreadsheet programming. So, either someone will figure this out for me or I'll just take 3d10 with me and roll on the charts manually I guess. I'm done.
Also, yes, I do realize this a community support forum staffed by volunteers. But that doesn't mean that someone can't still fix it for someone else who doesn't have the time to learn because they don't want to spend what precious little free time they have on such a thing rather than on enjoyable activities.
Re: Calc Err: 502
Posted: Tue Aug 30, 2011 12:32 am
by RoryOF
This cuts both ways; why should we spend our precious free time (your choice of word) solving your problem, so you can go gaming? Cop yourself on!
I think an Admin should lock this thread.
Re: Calc Err: 502
Posted: Tue Aug 30, 2011 2:16 am
by ralgith
Perhaps for the same reason that I give people the answers they need where I'm a forum Volunteer for a PHP based forum site. Because I know how to do it and they don't. So no, it doesn't cut both ways. You choose to volunteer here to answer questions/give help. As I choose to do so where I am a volunteer staff member. Now, I wonder how anyone gets any help here if you guys are always this rude. Dissing my one hobby just because I don't want to learn spreadsheet programming is pretty up there in the rudeness department. That would be akin to your coming over to where I'm at and asking how to set permissions on your forum and me telling you that if you're too lazy to figure it out with just a couple pointers then you shouldn't be running a forum. Not everyone can learn everything. Instead of locking the thread, perhaps you could just ANSWER the frigging question w/out the rude comments. This is NOT how you run a support forum, community based or not.
Re: Calc Err: 502
Posted: Tue Aug 30, 2011 5:05 am
by ralgith
Yup, once I got past all the bickering rudeness I figured it out. But I didn't do it alone. I got help from somewhere else as well as this and combined the two. Its nice to have friends who have friends who know how to do stuff. The rudeness wasn't necessary, and because of it I'll find my help elsewhere from now on.
Thanks to those who actually made an honest effort to help, even if I didn't know what you were talking about, and sod off to those who were rude.