KSP Calculations Spreadsheet [inDev]

[GoSlash27]

Pretorian,

 My excel is just good enough to get me killed. Yes, I believe it is possible to do that... but I don't know how. I have an excel reference book at work. I'll see if I can find the answer on Monday.

 Best,
-Slashy

[Kerbart]

14 hours ago, Pretorian28715 said:

@GoSlash27I have a quick question for you. Firstly how good is your Excel?

Do you know if it is possible to refer to another Excel file by %Folder%\KSP-Calc\KSP_Calc.xls

Where %Folder% tells the OS/Excel to look in the root folder, kind of how KSP looks in GameData for mods?

You can use the INDIRECT function to build a path and refer to another file that way.

[Pretorian28715]

43 minutes ago, GoSlash27 said:

Pretorian,

 My excel is just good enough to get me killed. Yes, I believe it is possible to do that... but I don't know how. I have an excel reference book at work. I'll see if I can find the answer on Monday.

 Best,
-Slashy

Awesome, thought it could be done but not sure how, thanks in advance.

 

1 minute ago, Kerbart said:

You can use the INDIRECT function to build a path and refer to another file that way.

What is that??

[Kerbart]

12 minutes ago, Pretorian28715 said:

What is that??

The Indirect function converts a text into a cell range.

Suppose you want to get twice the value of cell A2. Normally, you'd do it this way: =A2*2
But with the indirect function, you'd do it this way: =INDIRECT("A2") * 2

Now, that seems like a stupid way to do it, and it is, in the above example. But let's say that the cell  A3 contains your "KSP Path"
Instead of hardcoding ='R:\my ksp folder\[example.xlsx]Sheet1'!$A$1 you can now write:
=INDIRECT("'" & A3 & "\[example.xlsx]Sheet1'!$A$1 and make your formula dependent on a single cell that contains the KSP path (instead of having to change it all over the place).

Obviously you want to make A3 a named range and name it "KSP_FOLDER" or something along those lines, but you get the point.

 

Edited July 15, 2017 by Kerbart
Cleaned up the quote box

[Pretorian28715]

1 hour ago, Kerbart said:

--snip--

 

Ok, so that make sense but can I for example vlookup from that? Is it just a case of using that to reference the table in the other file?

Edited July 15, 2017 by Pretorian28715

[Kerbart]

Yes, whatever comes out of indirect is regarded a regular cell range, so you can use it inside a vlookup without problems

[Pretorian28715]

17 hours ago, Kerbart said:

Yes, whatever comes out of indirect is regarded a regular cell range, so you can use it inside a vlookup without problems

Just had a thought, not sure that this is what I need, but seems to be in the right direction.

What I'm after is a way of telling Excel to look in the folder where the file is, for another file.

Think it is some this like the following, but not sure.

E.g. If I have a file @ C:\KSP-Calc\example1.xls

What I want is to tell Excel %folder%\example2.xls

Where %folder% = c:\KSP-Calc

Hope this clarifies what I mean.

 

Thanks in Advance.

[Kerbart]

@Pretorian28715 Unless you define a system variable there's no way for Excel to figure out where KSP resides. With a "copy & paste" install there's not even a registry key you can harvest for that.

Theoretically you could write a macro to search for ksp.exe and populate a cell with that folder, but how can you be certain it's the right ksp folder (you might very well have multiples).

The simplest and most reliable solution, in my mind, is to populate a cell that contains your "ksp path"

[Pretorian28715]

2 hours ago, Kerbart said:

@Pretorian28715 Unless you define a system variable there's no way for Excel to figure out where KSP resides. With a "copy & paste" install there's not even a registry key you can harvest for that.

Theoretically you could write a macro to search for ksp.exe and populate a cell with that folder, but how can you be certain it's the right ksp folder (you might very well have multiples).

The simplest and most reliable solution, in my mind, is to populate a cell that contains your "ksp path"

No that's fine I was thinking for this calculator. For example I could add a spreadsheet of KSP parts. I just used the KSP folder as an example of what I meant. Also why I tried to clarify with my earlier post. 

I think Module Manager is a better example. It will search the GameData folder for .cfg files. What I want to do is instead of having to specify the fullpath to the other files I might add, just tell it to look in the folder it's in. 

This is not a Mod for the game it is a Tool for anyone to use. Which is why I might want to add to it. So I can create a KSP-Calc folder and put all the files in there and refer to them wit a short term as opposed to a full filepath.

[Kerbart]

1 minute ago, Pretorian28715 said:

No that's fine I was thinking for this calculator. For example I could add a spreadsheet of KSP parts. I just used the KSP folder as an example of what I meant. Also why I tried to clarify with my earlier post. 

I think Module Manager is a better example. It will search the GameData folder for .cfg files. What I want to do is instead of having to specify the fullpath to the other files I might add, just tell it to look in the folder it's in. 

This is not a Mod for the game it is a Tool for anyone to use. Which is why I might want to add to it. So I can create a KSP-Calc folder and put all the files in there and refer to them wit a short term as opposed to a full filepath.

Assuming you want to point to various locations in such a file, and assuming I understand correctly what it is you want, here is what I would do:

1) Put the path in a "KSP CALC LIBRARY FILE" cell. Why in a cell? Because it's easy to update for the end user, so they can put their own location for said file there.

2) Use Names for all specific ranges in the KSP calc file, using the INDIRECT method stated before. So you can have a ksp_planets range, a ksp_engine range, etc.

[Pretorian28715]

20 minutes ago, Kerbart said:

Assuming you want to point to various locations in such a file, and assuming I understand correctly what it is you want, here is what I would do:

1) Put the path in a "KSP CALC LIBRARY FILE" cell. Why in a cell? Because it's easy to update for the end user, so they can put their own location for said file there.

2) Use Names for all specific ranges in the KSP calc file, using the INDIRECT method stated before. So you can have a ksp_planets range, a ksp_engine range, etc.

A) Damn that was fast.

B) Thanks for that, I was trying to avoid 'User defined fields' in that sense, as 'User Error' tends to creep in. But I might have to go with that.

C) DAMN that was fast

Edited July 16, 2017 by Pretorian28715

[Kerbart]

10 hours ago, Pretorian28715 said:

A) Damn that was fast.

I was lucky about the time I logged in. Also; I like to think I know a lot about Excel. There are certain things I'm confident answering without checking (or merely a quick "did I get that right" kinda check, without having to look anything up).

[Pretorian28715]

11 hours ago, Kerbart said:

I was lucky about the time I logged in. Also; I like to think I know a lot about Excel. There are certain things I'm confident answering without checking (or merely a quick "did I get that right" kinda check, without having to look anything up).

Well thanks for you help you have given me something to work with, which is good to know. Might be able to expand how I want to.

[Kerbart]

2 hours ago, Pretorian28715 said:

Well thanks for you help you have given me something to work with, which is good to know. Might be able to expand how I want to.

Let me know if anything comes up!

[Cadet_BNSF]

What is TheVerse?

[Pretorian28715]

19 hours ago, Cadet_BNSF said:

What is TheVerse?

The Star System where the TV series Firefly took place. 

[XLjedi]

Hmm... an Excel spreadsheet.

 I'll try to take a look at it, as I've been known to do stupid things in Excel. Like pull the part data from craft files to analyze.

 

[XLjedi]

On ‎7‎/‎15‎/‎2017 at 9:34 AM, Kerbart said:

The Indirect function converts a text into a cell range.

Suppose you want to get twice the value of cell A2. Normally, you'd do it this way: =A2*2
But with the indirect function, you'd do it this way: =INDIRECT("A2") * 2

Now, that seems like a stupid way to do it, and it is, in the above example. But let's say that the cell  A3 contains your "KSP Path"
Instead of hardcoding ='R:\my ksp folder\[example.xlsx]Sheet1'!$A$1 you can now write:
=INDIRECT("'" & A3 & "\[example.xlsx]Sheet1'!$A$1 and make your formula dependent on a single cell that contains the KSP path (instead of having to change it all over the place).

Obviously you want to make A3 a named range and name it "KSP_FOLDER" or something along those lines, but you get the point.

 

 

@Praetorian28715  OK, I looked at your spreadsheet...

@Kerbart IIRC, the issue with INDIRECT is that the referenced workbook would need to be open else the associated values will not update.

There are ways to use INDIRECT in conjunction with the CELL formula to return the filename which includes the activeworkbook path info... to avoid having to input a KSP path.  For instance, this formula in cell A1 that simply refers to itself:  =CELL("filename",A1) would return filename and path, that could then be parsed with other cell formulas to just extract the path info to use with INDIRECT.  Again, I think you get stuck on INDIRECT not updating automagically unless the referenced workbook is actually open.

There are also ways (using VBA) to pull data into your spreadsheet directly from a text file or even a closed excel workbook...  The downside with adding VBA macros to your spreadsheet is that you'd have to specifically save the excel file as a .xlsm file... Which is not a big deal for most; but does require users to enable macros when they open the workbook.

I've built excel VBA gadgets for instance that will parse a .craft file and give me the part list details.  In setting that up, I did spend a little time building the stock part database in excel.  I had my reasons for wanting it this way as opposed to an in-game part list mod.

[Kerbart]

36 minutes ago, XLjedi said:

 

@Praetorian28715  OK, I looked at your spreadsheet...

@Kerbart IIRC, the issue with INDIRECT is that the referenced workbook would need to be open else the associated values will not update.

(...) There are also ways (using VBA) to pull data into your spreadsheet directly from a text file or even a closed excel workbook...  The downside with adding VBA macros to your spreadsheet is that you'd have to specifically save the excel file as a .xlsm file... Which is not a big deal for most; but does require users to enable macros when they open the workbook.

If I were running a solution for myself in Excel (I run my calculations in Python, but I digress) I wouldn't have a problem with running VBA macros but for a shared solution many will find it problematic, given the security risk. Personally I'd be very reluctant opening an xlsm or (even worse) an xlsb file as there's no guarantee that what you're inviting over the internet is clean.

Given the need for reference tables an alternate solution, when building something within the Microsoft Office framework, would be to consider building something in Microsoft Access. But that brings the VBA security issue back, of course.

[Pretorian28715]

On 27/07/2017 at 7:50 PM, XLjedi said:

--Snip--

 

On 27/07/2017 at 8:35 PM, Kerbart said:

--Snip--

Thanks Guys [or Gals], I like the input, I am however going for simplicity, if I can keep it to Excel functions [I don't program at all I'm afraid, never got in to it, Time], and also to reduce User input to a minimum required for each sheet.

Parts List??? is it up to date?? if so can I have it?

[XLjedi]

20 minutes ago, Pretorian28715 said:

Parts List??? is it up to date?? if so can I have it?

Here ya go...  KSP Parts in Excel

I believe the list is complete for the stock parts, although...  I don't think I have that new telescope part, as it was just added.  I have a bunch of other data that I'm still collecting/organizing in the file, but the part list itself is complete I think, and it has the .craft file part name, the long part name from the SPH/VAB, and associated pictures for each part, which is nice.

 

[Pretorian28715]

18 minutes ago, XLjedi said:

Here ya go...  KSP Parts in Excel

-- Snip --

 

Thank you.