Greyslayer
09/13/02 11:38 PM
216.14.192.226
|
A slow day at work for me had me creating a spreadsheet for use with battletech. I have got it so that if you enter the variant name of a mech it will put in the columns the mechs name (such as if its an ARC-2R then Archer will be in the name column), the tonnage of the mech, the Price in C-bills of the mech, the BV, the CV and the maintenance value under an old system (pretty much just 10% of cv per week).
What I have done is also create an entry of 0 instead of a mech variant in my data table to clear all the #NA entries if no data is entered unfortunately its changed all the places where #NA appears to 0. It doesn't look right and my question is as follows:
how would I change the following formulae to represent this (if b2 (variant) equals h2 (a known empty cell) then A2 equals h2 (or empty))
=VLOOKUP(B2,'Data Table'!A:G,2)
Just for your reference 'Data Table'! is a tab location where the data is stored rather than have on the same sheet. Its more or less just to clean up a little on the presentation side of things (much like removing the 0s is cleaning up, with the 0s I can run totals of price, tonnage, bv, cv and maintenance without them I cannot since the #NA interferes with the process). I might later run a process that calculates the updated BV according to the gunnery and piloting supplied (I held a convention using a simple spreadsheet doing this so I don't need help with that just cleaning up the damn cells!  ).
Anyone here a excel guru who can point me exactly in the right direction?
Greyslayer
|
NathanKell
09/14/02 04:56 PM
24.44.238.62
|
Use "" (double quotes) instead of zero. This works if the cell can be text. Or...
Hit CTRL-1 (format->cell) and go to custom format, then do ### in the custom box. That should, IIRC, have it only display the number, not zero.
Method 1 is preferred (since I know it works).
EDIT: I realize now that method 1 is for IF statements (or something that returns a value), not something to be put in a cell. You could thus have your formula be the following:
if(a2,vlookup(xxyyzz),"") which, if a2 is not blank (i.e. non-zero, i.e. true) will look up your value, else return complete blankness.
-NathanKell, BT Space Wars
Question with boldness even the existence of a God; because, if there be one, he must more approve of the homage of reason, than that of blind-folded fear.
Thomas Jefferson
Edited by NathanKell (09/14/02 06:26 PM)
|
Greyslayer
09/14/02 07:36 PM
63.12.145.32
|
#VALUE!
=IF(B2,VLOOKUP(B2,'Data Table'!A:G,2),"")
The #VALUE is what I get when I use the first method and look up something (though it is blank when I have B2 blank), if B2 has something in it though it returns the above problem.
Thanks so far though,
Greyslayer
|