Enabling and Disabling Calculations

Hello! I'm pretty much as noob as they get in VBA programming, but I am guessing that this is the section I'm most likely to get any help.

I have an excel sheet that works as a balance chart for my "League of Legends" custom character. For those who don't know what it is it is basically a character that can go from level 1-18 and purchase 6 items.

I need your help with the Item section. I have the Items in place in the sheet, but I want to easily be able to enable or disable one or more items from the overall calculations of the character's stats. Now by default my character has 6 items with all the stats they provide from level 1. This is not optimal as I want to be able to see how the character scales through the levels with different items. I would like to have a drop down list next to the item in the sheet where it either says "Enabled" or "Disabled", where the disabled option keeps the item in the sheet, but all numbers and values that item provides are removed from the overall calculation of the character's stats.

I hope my description is somewhat understandable and also possible to solve.

Thanks in advance

Hi,

I think it's doable. Just create dropdown list (using menu/data/validation) near each item and add an IF function to the function that uses the items as an arguent. Something like this

Please Login or Register  to view this content.

In the example A1 is where the dropdown list of enabled/disabled is located.
Can you upload the worksheet here? It would be a lot easier to work with.

Originally Posted by RHCPgergo

Hi,

I think it's doable. Just create dropdown list (using menu/data/validation) near each item and add an IF function to the function that uses the items as an arguent. Something like this

Please Login or Register  to view this content.

In the example A1 is where the dropdown list of enabled/disabled is located.
Can you upload the worksheet here? It would be a lot easier to work with.

I think i uploaded the balance sheet now, take a look and see whats possible The sheet is not at all complete, but I think it serves its purpose for this issue

bumping for answers

Hi,

this is my solution. I didn't fully understand how you calculate cells other than adding them, but this method shouldn't affect that. Just set "Enabled" cells to "Disabled" to try. It's not a very elegant solution, I created extra cells to help with the calculation. You can hide those rows if you wish.

How is attack speed and movement speed calculated? Because if an item adds a certain percentage to movement speed, you can just use it as =X*0.12*0.15 (where X is basic movement speed, 0.12 and 0.15 are two different items' movement speed bonus in percentage.

Originally Posted by RHCPgergo

How is attack speed and movement speed calculated? Because if an item adds a certain percentage to movement speed, you can just use it as =X*0.12*0.15 (where X is basic movement speed, 0.12 and 0.15 are two different items' movement speed bonus in percentage.

Attack speed and movementspeed i spent most of last night trying to figure out. The problem i encountered was that i want the attack speed of the last item to calculate the combined attack speed of both the default attack speed PLUS the additional attack speed gathered from items. I basically want the last item to increase the attack speed by 55% of the combined attack speed after all the other items have been added to the number as well. I believe my "code" does that :P But im not entire sure, lol. The same goes with movement speed. Some movement speed bonuses are precentage, others are flat numbers. I want the percentage to calculate after the flat numbers have been applied. Not sure if i can do this without hardcoding it, if u get my drift.

Also, is there a way of hiding content without deleting it? The cells you made sort of make the spreadsheet look somewhat unappealing :P Thanks so much for the help though, it was really great! Now i know more about how this is done