Is there support for the Robot design Excel Spreadsheet in your downloads?

[Commander Truestar]

Hello,
I wanted to try your Robot design spreadsheet found on your site: https://www.mongoosepublishing.com/pages/downloads-htm


So, I downloaded the file, launched it and decided to start trying it out
I entered a Name, Chassis size and Locomotion type......but, the spreadsheet failed to generate any values.
See below for obvious value-based Excel errors:

So, I am wondering if there is any support for this spreadsheet?
Has anyone else encountered this and is there a fix for the issue?

 

[MongooseMatt]

I'll have a chat to the creator!

 

[MongooseMatt]

We think this might be an incompatibility with the XLOOKUP function - what software (and version) are you using to look at the spreadsheet?

 

[Commander Truestar]

We think this might be an incompatibility with the XLOOKUP function - what software (and version) are you using to look at the spreadsheet?

Actually, it is exactly as downloaded from your site:
File = "RobotDesignWorksheetDownload_v1_51.xlsx"

I look forward to your findings

 

[MongooseMatt]

But which software are you using to view the spreadsheet? That is the important bit

 

[coliver988]

But which software are you using to view the spreadsheet? That is the important bit

which version of Excel and which operating system are you using is what is being asked. Excel acts a bit differently in some cases based on OS and version running.

Or are you opening it with another spreadsheet program - Libre Office, Excel online, etc. That sort of thing. Because while the spreadsheet may work on Windows 11 running the latest version of Excel, it may act differently on Windows 10 and an older version of Excel. That sort of thing. And yeah, I spent a year as tech support. My manager said I was the worst one she ever saw

 

[Commander Truestar]

I am using Windows 11 Build 26100.4652
I have a Intel(R) Core(TM) i5-1035G1 CPU @ 1.00GHz (1.20 GHz) installed
I have 12.0 GB (11.7 GB usable) installed

As for the software....I stated above that I was using Microsoft Excel. So, comments about Libre Office will be ignored.
As I said before, I am using Microsoft Excel Microsoft Excel 2016 MSO Version 2506 build 16.0 19825_20076 (64 bit)
And that installed directly on my PC in order to maintain what bandwidth Microsoft drains for their products.

When I downloaded the worksheet from the Mongoose website, I made no changes except:
The name of the 'droid to be designed

The chassis and legs were left at Size =5 and # of legs =2 (Size 5)

 

[coliver988]

2016 Excel? that may be the issue then. it is a decade out of date and some macros and functionality may have changed.

Just a guess there though.

Edit: Sadly one of the 1st things a tech support person askes (after "have you turned it off and on again") is are you up tp date on the software?

 

[MongooseMatt]

2016 Excel? that may be the issue then. it is a decade out of date and some macros and functionality may have changed.

It may mean something that I looked at '2016' and thought 'hmm, just a few years, that is very recent'

This will indeed be the issue. It needs to be Excel 2019 or above, or a current Office 365. 2016 will not work, because it doesn’t support XLOOKUP.

 

[Badenov]

It may mean something that I looked at '2016' and thought 'hmm, just a few years, that is very recent'

This will indeed be the issue. It needs to be Excel 2019 or above, or a current Office 365. 2016 will not work, because it doesn’t support XLOOKUP.

I guess my Home and Student 2007 version of Excel has no hope. But it's just a matter of elbow grease to change the xlookup to vlookup older versions used, and poof, it works. The formula in E4 of the design sheet becomes: =VLOOKUP(B4,Size,3,FALSE)

If all the rest are that easy, it shouldn't be a problem for those of us on lower TL machines.

 

[Badenov]

I guess my Home and Student 2007 version of Excel has no hope. But it's just a matter of elbow grease to change the xlookup to vlookup older versions used, and poof, it works. The formula in E4 of the design sheet becomes: =VLOOKUP(B4,Size,3,FALSE)

If all the rest are that easy, it shouldn't be a problem for those of us on lower TL machines.

So this is weird. I tried to save my modified worksheet partway through and excel crashed. Maybe it didn't like saving the unfamiliar commands? When it restarted, it had failed to save any of my changes, so I will mod the whole sheet before I save.

Edit: One of you with modern Excel can do this, my old version crashes when I try to save progress, and since I don't use robots at all, I have no dogs in this fight.

 

[Commander Truestar]

Hmm,
I changed E4 to "=VLOOKUP(B4,Size,3,FALSE)" and that seems to have resolved that cell's issue
However, I see XLOOKUP and other modern commands in the cells:
E2, E7, E19, E20, E36 thru E56, P30 thru 34, P36 thru 56 and P91

I'm looking at the formulae now to see if I can't resolve the issue, but hope you can make some recommendations?

 

[Badenov]

Hmm,
I changed E4 to "=VLOOKUP(B4,Size,3,FALSE)" and that seems to have resolved that cell's issue
However, I see XLOOKUP and other modern commands in the cells:
E2, E7, E19, E20, E36 thru E56, P30 thru 34, P36 thru 56 and P91

I'm looking at the formulae now to see if I can't resolve the issue, but hope you can make some recommendations?

So, yeah, all the instances of xlookup change to vlookup. The general case is vlookup(value to be looked up,table used to look up,column from table for return value,whether approximate matches are OK). Now the table used to look up is normally a range, but they've named the range of sizes 'size', so just putting Size there is OK for the table. There's several named tables in the orange tabs, those will almost certainly all be used for various lookups. One of the complicated bits is in cell N4 of the Blank sheet because it's got both nested IF statements, the xlookups to vlookups, but also the xlfn.CONCAT statements are different in old excel. In old excel, you just run the statements together with an ampersand (&) like so: =IF(VLOOKUP(B4,Size,5,FALSE)=0,"",IF(VLOOKUP(B4,Size,5,FALSE)>0,"Large (+"&VLOOKUP(B4,Size,5,FALSE)&")","Small ("&VLOOKUP(B4,Size,5,FALSE)&") "))

There's a few other glitches like the locomotion selection pulldown being broken for me, but the fact that I can't save progress and it randomly crashing and losing all progress makes it wildly frustrating, which is why I threw in the towel. Excel normally saves before it crashes, but in this case, the saved files are unopenable for me.

 

[Badenov]

It looks like the syntax for _xlfn.XLOOKUP is _xlfn.XLOOKUP(cell to be looked up,range to be checked (named as described above[column header for lookup column - old excel always uses the first column for lookup]), column header for results column (syntax as previous where old excel just uses the actual column number)*,true or false if approximate table values are OK - go with false here to be safe).

*you can apparently do math on the output intenal to the _xlfn.XLOOKUP statement. The same cannot be done in vlookup, but can be done to the result of vlookup just fine, so _xlfn.XLOOKUP($O$2,Armour[TL],Armour[Slots]*B7*E4,,,) becomes vlookup($O$2,Armour,5)*B7*E4

 

[Badenov]

The little square here:


can be pulled down with the little downarrow and it will list all the named ranges in the document. These are what go in the second part of the vlookup statement. ('Size' in =VLOOKUP(B4,Size,3,FALSE))

 

[Commander Truestar]

The little square here:

View attachment 6643
can be pulled down with the little downarrow and it will list all the named ranges in the document. These are what go in the second part of the vlookup statement. ('Size' in =VLOOKUP(B4,Size,3,FALSE))

Thank you very much for the recommendations.
Sadly, I have to work on my set list for my concert in the coming WorldCon, so I have to concentrate on that.
I also need to finish writing two songs and learning a cover.

So, I may not be back at this for two weeks or so

 

[Badenov]

Thank you very much for the recommendations.
Sadly, I have to work on my set list for my concert in the coming WorldCon, so I have to concentrate on that.
I also need to finish writing two songs and learning a cover.

So, I may not be back at this for two weeks or so

I'm glad to help, let me know if you need any more assistance when you get back to things, it sounds like you've got a busy time ahead.