Terry Mixon
Emperor Mongoose
I’m sorry to hear that. Hugs.
Excel Traveller Trade
- Thread starter Arkathan
- Start date
Terry Mixon
Emperor Mongoose
I'm no excel guru, but I think I made it work on a sheet I modified. First, I added two new columns so that there is both a Buy and Sell DM for BOTH the origin world and the destination. It was easy enough to change where the visible part of the spreadsheet pointed to and isolate the two so the codes for the correct world are being used for selling and buying as appropriate.
Then I looked at the trade code modifiers and there were only two fields with potential negative modifiers. I changed both of them to check for negative modifiers and use them before getting the max of the positive numbers
Common Consumables BUY: =IF(N3,-4,MAX(IF(N2,3,0),IF(N19,2,0),IF(N7,1,0)))
Radioactives SELL: =IF(N2,-3,IF(N15,-2,MAX(IF(N11,3,0),IF(N9,1,0))))
For the issues I was seeing, that fixed it for me, but as you said earlier, there are other implications for the changes. I just wanted to share what I'd done in case it proved helpful.
.
EDIT: Because only having one roll for buying AND selling was bugging me bad, I added a separate cell for the selling roll and then referenced it in the sales price chart. It was easier than I expected and now one calculation gets buying and selling in one go with appropriately different results for the ORG and DEST.
Attachments
Last edited:
deHap
Mongoose
Maybe possible you share your edited version?
Terry Mixon
Emperor Mongoose
I likely broke some of the elements that I don't use in making these changes. Also, I don't want to muddy the waters. It would be far better if the main spreadsheet were updated so that we all have access to the same file. This is Arkathan's baby and he will likely get around to making the changes when life cooperates.
Terry Mixon
Emperor Mongoose
As part of giving back, I took the time to add the changes that I'd made to the trade spreadsheet for myself back to the master sheet. All the modifications were to the Trade Goods tab and should not affect other tabs if you are working on a version of your own and want to migrate the source data tabs you are working with over to this sheet.
Here are the changes I made:
If you note anything broken, I will fix it. Otherwise, may the trade winds be always at your backs.
Here are the changes I made:
- Separated the origin and destination trade DM calculations so they were being appropriately utilized
- Separate buying and selling rolls
- Tweaked the few DM calculations that could have negative DMs to correctly use them in the few instances where they would present themselves
- Made sure that the broker skill was used for regular trade and streetwise for the illegals trade
- Renamed the nomenclature to "Origin System" and "Destination System" for clarity
If you note anything broken, I will fix it. Otherwise, may the trade winds be always at your backs.
Last edited:
Corridor Chronicler
Banded Mongoose
This is an excellent and wonderful tool. The only 'bug' I found is that the formulae are set up for subsectors up to 32 worlds and so it doesnt work fully for, as an example, Tobia/Trojan Reaches which has 34 systems. The data is there but you have to edit the formulae that look to the sector tabs to increase the range searched. And I am not sure I am doing it right as a drag/drop isn't working for me. It also requires moving the Starport codes in the bottom right corner of the Planets Tab and that is breaking for me as well. But I am in LibreOffice not Excel.
Oh you also need to edit the data validity for cells Planets Tab H4 and L4 or else they wont refer to the newly expanded list of systems in the P19 table.
I also caught that Planets Tab R34 uses HLOOKUP(PurSub,INDIRECT("'"&Sector_Name&" HexInfo"&"'!"&"$A$1:$AU$33"),21) When its a sell reference and ought to be HLOOKUP(SelSub,INDIRECT("'"&Sector_Name&" HexInfo"&"'!"&"$A$1:$AU$33"),21)
I hope this is helping anyone.
Just a heads up anyway.
Oh you also need to edit the data validity for cells Planets Tab H4 and L4 or else they wont refer to the newly expanded list of systems in the P19 table.
I also caught that Planets Tab R34 uses HLOOKUP(PurSub,INDIRECT("'"&Sector_Name&" HexInfo"&"'!"&"$A$1:$AU$33"),21) When its a sell reference and ought to be HLOOKUP(SelSub,INDIRECT("'"&Sector_Name&" HexInfo"&"'!"&"$A$1:$AU$33"),21)
I hope this is helping anyone.
Just a heads up anyway.
Last edited:
Arkathan
Emperor Mongoose
I made the sheet for Spinward Marches. Others expanded it to work with other sectors.
I am no longer supporting this tool, so that I don't mess up functionality. It is open source now. Anyone who has a fix can post it.
Sounds like data validation needs to be expanded on the planet selection drop downs.
I am no longer supporting this tool, so that I don't mess up functionality. It is open source now. Anyone who has a fix can post it.
Sounds like data validation needs to be expanded on the planet selection drop downs.
Terry Mixon
Emperor Mongoose
I’ll take a look at it when I have time this weekend. If you want to post an updated version that corrects the issue, that would be even better.
Corridor Chronicler
Banded Mongoose
I apologise wholly if I came across as critical of your work or as though I was calling you out for additional input.
I did not intend that at all.
The work is amazing.
I was just pointing up some issues with the expansion for bug fixing purposes.
The core functionality works as intended.
Corridor Chronicler
Banded Mongoose
*coughI’ll take a look at it when I have time this weekend. If you want to post an updated version that corrects the issue, that would be even better.
I would
But I still can't get the extra sheets to work as I think they should. But I think its a VLookup issue in Libreoffice not returning the result anticipated. I will try it in GoogleDocs later in case that's a factor.
My current personal WIP version is a mess of notes and highlights and error checking and not something I can share.
The expansion work from other people volunteering and making changes is also amazing and I appreciate all the hard effort that has been put in.
I'm currently in a Trojan Reach campaign so this seemed a natural took to try to make use of.
Arkathan
Emperor Mongoose
Not at all. I was pointing out that I am not adding to this anymore, because, knowing myself as I do, I WILL break something that will mess up the multi-sector stuff. Anyone who has a fix is welcome to put it out there.
V & H lookup... that was before I discovered Index(Match). One of the hazards of learning entirely by doing until it works.
Corridor Chronicler
Banded Mongoose
I think that Index and Match might work better, there is something with the formatting or formulae in the sector data cells that is causing LookUp to hiccough.
Back to ChatGPT to explain it all to me.
I hope your life is better than the earlier posts about some difficult situations (without repeating your earlier disclosure).
For what its worth, using Index and Match on Planets Tab in cells H43-45 seems to fix this first problem. I am looking at other areas so I can check each column and row as the names look right but the trade codes are duplicating in some columns. If I can get a version that seems okay I will clean it up and post it myself, but if anyone else is working along feel free to do it first so we can compare.
Last edited:
Corridor Chronicler
Banded Mongoose
Still plugging away on this and lots is coming together. Changing LookUp to Index and Match on the Planets Tab and trying to save time with some conditional references. But this makes my eyes bleed.
IF(K53="","",INDEX(INDIRECT("'"&Sector_Name&"'!"&"$AK$2:$AK$1281"), MATCH(INDIRECT("R[-2]C", 0), INDIRECT("'"&Sector_Name&"'!"&"$A$2:$A$1281"), 0)))
One thing I did discover, there is a section of the Trojan Reach data missing. It looks like its been accidentally deleted and none of the versions posted here include it. It deletes all information from Hex columns 0909, 10 and 11 through to 1140 from the sector 'map'. Its columns BB-BD in the most recent version.
If anyone has that data can they post it?
If not I will try to recreate it after I finish the formulae on the Planets Tab.
IF(K53="","",INDEX(INDIRECT("'"&Sector_Name&"'!"&"$AK$2:$AK$1281"), MATCH(INDIRECT("R[-2]C", 0), INDIRECT("'"&Sector_Name&"'!"&"$A$2:$A$1281"), 0)))
One thing I did discover, there is a section of the Trojan Reach data missing. It looks like its been accidentally deleted and none of the versions posted here include it. It deletes all information from Hex columns 0909, 10 and 11 through to 1140 from the sector 'map'. Its columns BB-BD in the most recent version.
If anyone has that data can they post it?
If not I will try to recreate it after I finish the formulae on the Planets Tab.
Corridor Chronicler
Banded Mongoose
Okay, here is a very much WIP.
But if it works I will try to add Corridor Sector next.
But if it works I will try to add Corridor Sector next.
Terry Mixon
Emperor Mongoose
Go, Gadget, go! Thanks for helping this project grow.
Corridor Chronicler
Banded Mongoose
Found and corrected some silly copy/paste errors in Column H of the Planets Tab.
Had a stab at creating Corridor sector data. Harder than I thought, even following the well presented instructions.
Any comments, debug or other warmly welcomed.
Had a stab at creating Corridor sector data. Harder than I thought, even following the well presented instructions.
Any comments, debug or other warmly welcomed.
Corridor Chronicler
Banded Mongoose
Oh, this isn't too hard. He said
I can do this. He said
I should add Deneb so you could work from the Trojan Reach to Corridor in one go. He said
What could go wrong? He honestly believed.
As it turns out, quite a lot can go wrong.
I have had a stab at Deneb. Now Deneb has subsectors that contain even more systems so some of the tables needed extending on the Planets Tab. And that meant the ranges needed extending in some cases too.
And, for some reason I am too tired to fathom, Million and Pretoria Sectors only display their hex location and not the system names in the Buy - Buy Hex - Sell - Sell Hex table.
I dont have time to come back to this for a while but here is another WIP file for all your happy trading spreadsheet people.
Most of it works. Arkathan's systems have not been messed with. You just cant use it for Million/Deneb or Pretoria/Deneb until I work out what I have done wrong.
Happy Trading!
I can do this. He said
I should add Deneb so you could work from the Trojan Reach to Corridor in one go. He said
What could go wrong? He honestly believed.
As it turns out, quite a lot can go wrong.
I have had a stab at Deneb. Now Deneb has subsectors that contain even more systems so some of the tables needed extending on the Planets Tab. And that meant the ranges needed extending in some cases too.
And, for some reason I am too tired to fathom, Million and Pretoria Sectors only display their hex location and not the system names in the Buy - Buy Hex - Sell - Sell Hex table.
I dont have time to come back to this for a while but here is another WIP file for all your happy trading spreadsheet people.
Most of it works. Arkathan's systems have not been messed with. You just cant use it for Million/Deneb or Pretoria/Deneb until I work out what I have done wrong.
Happy Trading!
ColinD
Cosmic Mongoose
Amazing! Great work, most of my adventures take place on the Solomani Rim, although I am looking at Rim Expeditions but I use your spreadsheet to see how the Trade game can work. How do your players navigate this? Do they have a small Scout or a larger Merchant type?
Arkathan
Emperor Mongoose
Thanks.
They have a modified FASA Chameleon Commerce Raider. With two small vehicles packed for shipping, they have around 53 tons to play with. Generally pays the maintenance, sometimes meets the payroll.
There are instructions, buried back in here somewhere, that tell you how to get data from the Traveller Map and insert it as a new Sector.
But as shown above, not all is rosy.
