Lich vrs Excel...Excel winning...

[Aotrs Commander]

I must request the aid of the Excel knowledgable denizens of the playground. My usual technical assistance are unavailable for the weekend, and I really want to press on while the iron is hot...

I am currently wrestling with Excel (2007 if it makes any difference), not a program I've got a lot of experience with. I'm actually converting the points values of my...many...starfleets after a major rules revision - having decided I really, ree-heeally didn't want to do it by hand, I've been trying to use Excel. After a bit of a rocky start, I've about got somewhere with my various formulas so changing things around is a minimum of cell-filling in.

And I thought to myself, "I wonder if'n I can be really clever and stick the weapons data in while I'm faffing around?"

I.e. what I would like to do is take an entry of "Beam 1" and have it lookup the mass or hit points and points cost and stick it in the revelvant cells.

I think the best way to describe this is to be illustrative.

So basically (with square brackets representing cells), my weapon data table looks something like this:



[A ] [ B] [ C] [ D]
[ 1] mass hp PV
[ 2] [ 1] [ 1] [ 3]
[ 3][Beam 2 ] [ 2] [ 1] [ 12]
[ 4][Beam 3 ] [ 3] [ 1] [ 27]
[ 5][Beam 4 ] [ 4] [ 1] [ 48]
[ 6][Beam 5 ] [ 5] [ 1] [ 75]
[ 7][Beam 6 ] [ 6] [ 1] [108]
[ 8][Beam 7 ] [ 7] [ 1] [147]
[ 9][Beam 8 ] [ 8] [ 1] [192]
[10][Beam 9 ] [ 9] [ 1] [243]
[11][Beam 10 ] [10] [ 1] [300]


My design sheet looks something like this.



[D ] [ E] [ F] [ G] [ H] [ I] [ J] [ K]
[27] number mass hp PV net net net
of guns per per per mass hp PV
gun gun gun
[28][B][Beam 4 ] [ 2] [ 4] [ 1] [ 48] [ 8] [ 2] [96]


Currently, you have to manually input the values for the cells in bold, and my formula calculate the rest. What I would like to be able to do is change that so you only have to put in the weapon name (i.e. Beam 4) and the number, and have it calculate the rest. Thus:



[D ] [ E] [ F] [ G] [ H] [ I] [ J] [ K]
[27] number mass hp PV net net net
of guns per per per mass hp PV
gun gun gun
[28][Beam 4 ] [ 2] [ 4] [ 1] [ 48] [ 8] [ 2] [96]


Now, there are lots of other weapons types (well, no more than 15, actually), with different data. So what I'm angling towards being able to just go down and type in "Beam 3 times 2, Beam 4 times 1, Pulse 2 times four" and so on and have the table supply the relevant data.

Okay so far?

Right, all this was relatively straight forward using the previously discovered LOOKUP command, and indeed all when swimmingly until I reached Beam 10 on my data table.

Because it then, when called to look for "Beam 10" on said data table, looks up Beam 1 and stops. (It took me bloody ages to figure out what was going wrong...)

So does anyone know what command - if any exists - I need to get Excel to look up exactly [text string 10] from a list of [text string 1]:[text string 10]? MS help is as usual, totally worthless, and google searching didn't help either.

Now, as I typed this, I realised that one way round this is to use 'Beam 01' instead of 'Beam 1' etc, but that's a bit untidy.

Is this even possible? I mean, I could live with the '01's if it is necessary, but I'd rather see if I can get around it (if nothing else, it'll teach me somemore about Excel!)

I'd greatly appreciate it if anyone could shed some light on this matter.

[Pyrian]

Lookup is only valid when the key data is sorted. Thus, instead of:

Beam 1
Beam 2
...
Beam 10

You need:

Beam 1
Beam 10
Beam 2
...
Beam 9

Basically, the algorithm gets to "Beam 2" and figures it missed "Beam 10" and settles for "Beam 1".

[Aotrs Commander]

Lookup is only valid when the key data is sorted. Thus, instead of:

Beam 1
Beam 2
...
Beam 10

You need:

Beam 1
Beam 10
Beam 2
...
Beam 9

Basically, the algorithm gets to "Beam 2" and figures it missed "Beam 10" and settles for "Beam 1".

Ah-ha. Ta.

Funnily enough, I was just starting to wonder if that was the case. In the meantime, I've been playing with how to point cost the firearcs. So that basically, you'd type in say "F,FR,RR" and the function read that value turns it into a number (which is basically the number of 60 degree arcs) and then uses a (long) if statement to find out what column it looks up the PV for that weapon's number of arcs. Surprisingly - since the table is mainly permutations of F, FR, RR, RL, R and FL, once I sorted via alphabetical order, it worked.

I shall try sorting the other bits forthwith.

W00t. That works nicely... that's most of the weapons sorted then! (With some effort...)



Now, if I'm going this far, I may as well try and go all the way!

So, the next - and really quite hard bit - are the Torpedoes. They have a lot of variables, and I don't have a lot of space to could them all.

So, is it possible to extract bits of strings and convert them to numbers?

E.g. to extract the following highlighted numbers (and one text string) from a cell (seperately) and use them in calculations?




[Torpedo 1 (TL 6)] [1 F,R 4: T60 (x3)]


At the moment, torpedoes have to be done by hand (well, by changing the formula). If I can sort this as well, I'll be really chuffed!

[Pyrian]

So, is it possible to extract bits of strings and convert them to numbers?Sure! Mid and, if necessary, Search are the functions to extract inner portions of strings. It's easy if the digits are always in the same place, but can get quite complicated otherwise. Once you've got a string that contains only digits, the simplest way to convert it to a number is "/1".

[Aotrs Commander]

Right. Back again and probablly trying to be too clever for my own good!

I have about finished the basic (and not-so-basic!) stuff on my fleet list spread sheet, but now I'm coming to a minor irritant.

What I'm looking for is a way to reference a cell that will copy-and-paste but without $ locking it. A semi-mobile lock, if you will. (EDit: something like the move command does, but by copying instead of just moving around).

The easiest way to explain this, I think, is once again to be illustrative.

A lot of the system lines reference boxes at the top for each ship. (The bold bits below). In particular, the box labelled HFC, which toggles between HFC and HF (which basically sets the formula for dealing with fighters or for capital ships).




[D ] [ E] [ F] [ G] [ H] [ I] [ J] [ K]
[25] [HFC]
[26] [ 1]
[27] number mass hp PV net net net
of guns per per per mass hp PV
gun gun gun
[28] [ 2] [B][ 4] [ 1] [ 48] [ 8] [ 2] [96]


Currently, most of them say IF(E26="HFC",(do something),(do something else)).
Now, when you copy and paste those lines, it course moves the reference box.




[D ] [ E] [ F] [ G] [ H] [ I] [ J] [ K]
[25] [HFC]
[26] [ 1]
[27] number mass hp PV net net net
of guns per per per mass hp PV
gun gun gun
[28][Beam 4 ] [ 2] [ 4] [ 1] [ 48] [ 8] [ 2] [96]
[29] [ 2] [B][ 4] [ 1] [ 48] [ 8] [ 2] [96]


...which means you have to manually move the reference back. Which is fine for me, as I know what formula is supposed to go there, but it's not exactly other-user friendly.

I can't $ lock the reference, because obviously, they'll be multiple HFC boxes on one worksheet (e.g.):




[D ] [ E] [ F] [ G] [ H] [ I] [ J] [ K]
[25] [HFC]
[26] [ 1]
[27] number mass hp PV net net net
of guns per per per mass hp PV
gun gun gun
[28][Beam 4 ] [ 2] [ 4] [ 1] [ 48] [ 8] [ 2] [96]
[29] [ 2] [B][ 4] [ 1] [ 48] [ 8] [ 2] [96]



[D ] [ E] [ F] [ G] [ H] [ I] [ J] [ K]
[35] [HF ]
[36] [ 20]
[37] number mass hp PV net net net
of guns per per per mass hp PV
gun gun gun
[38] [ 2] [ 4] [ 1] [ 48] [ 8] [ 2] [96]
[39][Beam 3 ] [ 2] [B][ 4] [ 1] [ 48] [ 8] [ 2] [96]


So $ locking would make it worse as you'd have to reset all the forumla every time you started a new ship.

I've tried the various special pastes, but they don't solve the problem.



So, what I would like to be able to do is add a flag to a cell. So I can put in the first line (which happens to be the shield generators) something that says IF (cell=HFC, (do usual HFC formulas and set flag to 1),(proceed as normal)).

Each other system line would then say If(cell above flag =1,(do HFC formula), (do normal formula)).

Which would essentially mean the lines could be cut and pasted more easily.

(I have managed something similar (after much dint of effort!) with my armour layers; the ship has one mass of armour points, which are divided among one or more layers. I managed to get each line to look for the word "Armour" in the cell above and then take the armour value of that line and subtract the armour used on this line (giving you a running total). But this of course requires the formula to look for a particular, defined value, where as in the HFC case, it's only got a number relating to ship-space to look at).

Now, I'm not even sure this is possible to do, but it would be very handy. (I might even be able to do something to set the, say, total PV to look for the correct range of cells by telling it to look between two semi-fixed points.)
Is this even possible, or have I finally reached the limit of Excel's programming ability?

Or is there a more elegant solution I've missed somewhere?

[Jimp]

When I first read the thread's title I thought "The Lich King vs Excel from Excel Saga? and EXCEL is winning?" and braced myself for hilarity.
I am sadly disappointed :smallwink:.

[Pyrian]

I'm not entirely clear on whether I've grasped the problem, but it looks like you want a simple partial lock. See, E26 is unlocked, and $E$26 is locked, but you can also do $E26 which just locks the column, or E$26 which just locks the row. Is that it?

[Aotrs Commander]

Not really. (Been a day or two, but I've actually been busy narrowing down the REALLY HARD bits of torpedo and their magazines which have variable amounts of ammo and munitions types. I'm done with that - almost so I'm reallt close to finishing).



Let's try using some specific examples.

Right. Each ship record sheet has a HF/HFC box, which toggles the ship record cells from capital starships to fighters. This changes a fair few formula, but the one I'm most concenred about is for weapons. Cap ships measure their space in HF, and fighters in Gear Slots (where 4 Gear Slot = 1 HF). Thus, the lines which contain the weapons have a formula in thier box which says "If C5 = "HFC", then times the gun's HF values by 4 as it's now measured in Gear Slots". Each Ship record thingy is a series of about (what, 10-15 or so) rows (hereafter referred to as a Ship Record Profile, so we don't get mixed up with worksheets, which is another thing altogether) with the HF/HFC box (among othe things) at the top and a number of rows with the actual systems data filling the rest.

With me so far? Good!

The problem is, if you cut and paste the weapon ROWS within a given profile, it moves the reference, so the formula doesn't look for "C5" it looks for C6 or C7 or something, meaning you have to manually set it back. (Granted, if you're not building a fighter, that doesn't matter.) I.e., when you cut and paste, you move the reference in relation to that row.

Dollar locking (I.e. $C5, C$5 or $C$5) works for that particular ship profile, but when you cut and paste the ship record cells for the next ship in that fleet (as I don't want a seperate file for every ship class - I've got probably a hundred plus!), it then references the wrong cell. Now, because you've copied the whole lot from line (say) 4 down to (say) 15 down to 14 to 25, the second profile's formulas are now looking back up to C5 when we now want them to look at C15. (Which means you have set EVERY system box to the new value, not just the odd row whose position in relation to one cell has changed.)



What I would like is a way to put in the box to make it look for the right HF/HFC cell. I can think of several potential ways, but I don't know any relevant commands. The first one would be to have a flag that says, if this flag is active, do this. (You might need an unflag box at the bottom of each ship profile.) Or each cell looks at the one above and says, if that cell's flag is 1, my flag should be 1 too as so on.

Second, a way of saying "look for this cell". (Maybe a search above for the first cell that contains HFC? I.e. "look for a box that has HFC in it, and if you find it before you find a box that has HF in it, do this" or look for the first cell above named "Billy" and if "Billy" is HFC, then do this.)

Either - or some other easier thing I've missed way well be possible (maybe something to with turning each ship profile into an array an looking within that somehow) - but I don't know what relevant commands to use.

[SMEE]

Then you have to put $C$5 in the formula instead of C5. just like Pyrian told you...

Now, for duplicating this data, I suggest using the other sheets and naming them accordingly instead of having all of them in the same sheet.

[Aotrs Commander]

Then you have to put $C$5 in the formula instead of C5. just like Pyrian told you...

Now, for duplicating this data, I suggest using the other sheets and naming them accordingly instead of having all of them in the same sheet.

Sorry, I was half-way through my explanation when I acidently posted...hopefully now that I've actually finished it's more clear. I do know about $#$#, as that's how I've been referrencing all the weapons data, and I tried that sort of thing first. What I'm trying to accomplish I suspect is a) several orders of magnitude more complicated (perhaps way too complicated!) and/or b) not possible in Excel. But i don't know what it's limitations are. Hell, three Mondays ago I'd never used Excel in anger (and had to be told about the aforementioned $ing!)

That I've got as far as (at one point) being told to sod off by Excel for having formula too big (because I was trying to do too much in one cell!) in that short time is something of an accomplishment I think! (With a bit of reworking my sources, I actually simplified what I had down to make it work later - and work better!)

I'm grilling one of my mates who knows about spreadsheets too, so hopefully I can find an answer (or not) and move on with my Unlife...



Edit: Oh, I've tried some of the various Paste Specials and they didn't seem to help.

[Pyrian]

Ah, I see, now. :smallcool:

In that situation I would use a hidden column. (Note that this also works for formulas that grow too large!) Insert a column at some point, and have each of the cells in that column reference up to the appropriate HF/HFC, no $'s. Now you've got an HF/HFC column in every row, so every row can easily access it, no matter where you copy the set. Then, you can hide the column so it doesn't mess up how the table looks.

[Aotrs Commander]

Ah, I see, now. :smallcool:

In that situation I would use a hidden column. (Note that this also works for formulas that grow too large!) Insert a column at some point, and have each of the cells in that column reference up to the appropriate HF/HFC, no $'s. Now you've got an HF/HFC column in every row, so every row can easily access it, no matter where you copy the set. Then, you can hide the column so it doesn't mess up how the table looks.

Right. The thought of using a column like that had crossed my mind, actually, I just wondered if there was another, simpler solution!

I wasn't aware (or rather, it hadn't twigged my concious mind) you could hide columns, though. Had I known that, I could have saved myself a great deal of effort with those torpedo magazines by carrying one or two of the variables down!

Still, never mind, you unlive and learn! I think I can actually get the job done now!
Thanks.

[Pyrian]

Glad I could help! Yeah, it's certainly possible to have the formula itself look up the title row and from that derive the position of the header data, but I'd hardly call it simpler. :smallcool:

EDIT: If you want to see something silly, pm me your e-mail address and I'll send you an action game I wrote entirely in Excel (and in about one hour).