creating a game related excel spreadsheet and need help

[heavymetal2000]

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]

Ok so I managed to figure it out.

Using
=if(countif(av6:bv45,"weapon focus: "&a13&""),"focused","unfocused")

Did the trick.

 

[heavymetal2000]

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!

 

[Supahos]

Could you just put the drop-down in C1 and use it if b1 doesn't equal a b or c?

 

[randomizer]

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!

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]

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]

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]

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]

=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,""))))))

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

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.

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.

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.

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]

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]

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]

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.

 

[randomizer]

This one is ok: http/excelformulabeautifier.com/

 

[heavymetal2000]

Thanks for all the help randomizer. Appreciate it! As well as the link.