creating a game related excel spreadsheet and need help

heavymetal2000

Honorable
Oct 28, 2012
14
0
10,560
0
Ok so I've been working on a spread sheet for a gane i play (NWN)

Im looks g for help on a formula to do the following if it's possible.

I need to look in the range AV6:BF45
within that range I need to find out if a cell contains "weapon focus: A13"

A13 refers to a cell value which changes based on a drop down menu when you select your used weapon.

Being this way I need to formula to be able to read it as such.

Lastly if it does locate it, i need it to return my determined values "focused" or "unfocused"

If anyone can help at all thank you! If you need more specifics just lmk.
 

heavymetal2000

Honorable
Oct 28, 2012
14
0
10,560
0
Need some help.

Is there a non complicated way to have a cell show a specific value in it based on what another cell value us and if this value required is false show a drop down menu instead?

Basically

If A1 = A or B or C then B1 = A or B or C (The same value as in A1.
But if A1 = anything other then A or B or C Then B1 = drop down menu with list of options.

Can anyone help with This? Thanks!
 

randomizer

Distinguished
Moderator
Jul 24, 2006
882
0
19,260
62


You can do this with a VBA macro by adding or removing Data Validation (with a list of possible options) as required. I'd go with Supahos suggestion if possible though. You can set B1 = C1 if it is not A, B, or C, and then you select an option in C1.
 

heavymetal2000

Honorable
Oct 28, 2012
14
0
10,560
0
I'm trying to limit the cells so that the end result is polished and clean.

Having cells with values based on cells stacked on the sheet is what I'm trying to avoid.
Seems crazy excel and other programs have been around for quite a while now and there is no "show this list" function.

I'm not familiar with macros but I'll read up on it. Thanks so far guys.
 

heavymetal2000

Honorable
Oct 28, 2012
14
0
10,560
0
managed to do this with the INDIRECT() function.

Created a cell in my data sheet to decide what list was to be used.

=IF('3T Calculations(Manual)'!A13="Double Axe","DoubleAxe",IF('3T Calculations(Manual)'!A13="Two Bladed Sword","TwoBladedSword",IF('3T Calculations(Manual)'!A13="Dire Mace","DireMace",IF('3T Calculations(Manual)'!A13="","",IF(AND('3T Calculations(Manual)'!A36="",'3T Calculations(Manual)'!D3="large",'3T Calculations(Manual)'!C13="melee",'3T Calculations(Manual)'!C17<>"Large Dual",'3T Calculations(Manual)'!C17<>"Large"),'3t Misc'!A42:B42,IF(AND('3T Calculations(Manual)'!D3="small",'3T Calculations(Manual)'!C13="melee",'3T Calculations(Manual)'!A36="",'3T Calculations(Manual)'!C17<>"Large Dual",'3T Calculations(Manual)'!C17<>"Large",'3T Calculations(Manual)'!C17<>"Medium"),'3t Misc'!A43,""))))))

Then used INDIRECT(CellValue) as the source in my DV List

Now i need help again maybe.

i cannot figure out how to have the cell automatically go blank if the selection in another cell is changed.
Any Tips? Thanks!
 

Ralston18

Dignified
Moderator
In Excel you can use "Conditional Formatting" to control many aspects of a cell.

However, in your situation, you may need to have a duplicate column of cells for comparison purposes and if "Not equal" the desired cell goes to "blank".

Or perhaps some date and time stamp regarding a cell (or cells) entry. If changed with respect to some "master stamp" the desired cell goes blank.

Key is flagging that a cell has been changed and bringing that to the attention of another cell via some conditional. At which time that cell goes blank or changes to the color red perhaps.

 

randomizer

Distinguished
Moderator
Jul 24, 2006
882
0
19,260
62

That's not what I'd call polished and clean but if it works for you...


That might work initially, but then you'd need to keep the reference cells up to date to reflect a different change later. You'd also require a formula in the blanked cell, and I assume the purpose of it going blank is because it requires input, which would erase the formula.


There are really only two ways for a cell to respond to changes in another. Either the cell is dependent on another cell because of its formula and will update as the spreadsheet recalculates, or it is updated manually by a VBA macro in response to an event being raised (eg. Worksheet Change).
 

heavymetal2000

Honorable
Oct 28, 2012
14
0
10,560
0
That's not what I'd call polished and clean but if it works for you...
Sorry what I meant by polished to keeping unnecessary cells off of the main.
But by having another cell on my data sheet to somewhat simplify the way I got the lists to show up based on that criteria just helps keep the clutter down on my main sheet.

Is there a way to simplify the formula I provided?

And thanks for the link. I'll have a look at it and see if I can't get something working for me.
 

randomizer

Distinguished
Moderator
Jul 24, 2006
882
0
19,260
62
The formula can probably be made more terse, but I don't know if it can be made simpler. It's not complex as is, it's just long and unreadable unless you use a formula formatter.

Avoid using VBA if there's a simple way to do it without VBA. Eventually you'll hit a wall if your requirements become too complex though. The good thing is that you can do almost anything you want in VBA, even things that should never be done in VBA. I can personally attest to that :lol:
 

heavymetal2000

Honorable
Oct 28, 2012
14
0
10,560
0
Haha sounds good. I would like to learn some VBA though now that it's been brought up as a potential solution.

Formula formatter?
I'll have to look into this.
Meantime I'm trying to create cells based on cells based on other cells to simplify some things.
I'll just stuff it all in my data sheet.
 

Similar threads