Finding and filling custom cells with custom values.

[Durarara]

Hello,
I need some help on a matter: I want to make what is known as a complex "character sheet" for a Role-playing game in Excel. This is the problem I am facing (I will try to keep it simple).

There is an option for a person to pick a value from a drop-down list. I want excel to detect what value was picked, compare with a number in a set cell, and fill predefined cells with values from a separate sheet which contains a data table with values for each list value and number combination.

I also want to know a method to make excel overwrite a cell range with certain specific text, depending on the value picked (actually depending on the value picked from another drop-down list but that is just details).

Lastly there are three cells containing numbers (lets call them A, B and C). The user fills A with whatever number he wishes and B and C are with set numbers. B contains a quantity that must be reached before the number in C advances by 1. The user fills A, I want when the quantity he inputs reaches or surpasses the value set in B, for Excel to subtract the value of B from that of A in cell A (so, the value of cell A would be equal to A-B [in code: A=A-B]) and increase the value of C by 1 (C=C+1).

The only method I can think of is a near endless If/then/else cycle that's why I'm looking for other ideas. I am quite proficient with Excel, so do not worry to post your ideas, no matter how complex they may be. I am posting this question here, because I believe it would be easier to do it with VBA, at the moment I do not know how to program with VBA, but I have other programming experience and will learn VBA for Excel in the very near future, so please leave your suggestions.

[Richard Buttrey]

I don't understand your first request associated with pre-defined values from another sheet. You'd be better off uploading an example workbook showing before and after situations.

For your second request, can't you just put an IF test in the cell in which you expect to see the text?

For the third request you'll need a Sheet Change macro

Please Login or Register  to view this content.

[Durarara]

Thank you for the fast reply!

I've uploaded an example, the "before" sheet shows the default "starting" position and the "after" sheet shows what should be automatically filled when the user chooses an item from the drop-down list, colored in yellow. Specifically the other cells, colored in yellow, must change to the appropriate values.

The "Class Library XP Table" contains the numeric values that should be filled for each item chosen from the list. I hope you understand now, let me know if I should clarify further.

About my second request - I did, but I think it would need to be a re-e-e-e-e-e-e-eally long if cycle - the red text in the "after" sheet is roughly what should appear when another value from another drop-down list is selected, and the size of the text varies with each item on the list.

And about the third request - thank you!

[Durarara]

Let me just do a little *bump up*, I have the feeling the thread was buried dead. :P

[Richard Buttrey]

Hi,

I can see the After sheet but I don't understand how you arrive at the values for the yellow cells or the red text values. i.e. the rules you are applying to your model.

If you can explain quite clearly how you work out the values for these and other choices we may stand a chance, but at the moment the workbook is far too 'busy' and not easy or intuitive to understand.

[Durarara]

I understand, I'm sorry I have not made it clearer before.

Now I'm attaching a new example, where I have removed what's not important, I've officially left some things but I have whited them, so they are not visible. On the sheet "Class XP Table" you don't need the numbers that are in gray, but I've left them visible, if you need a size of data reference. I've also added comments, which I hope will help you understand everything.

I want to thank you again for all the trouble you are going through to help me!! sbowing_100-104.gif