Excel gurus, need some help

[NorCalAthlete]

Creating a spreadsheet for range quals and want to do a few things.

1. Having trouble with chaining conditional formatting/data validation lists. I have 3 fire table columns that populate via dropdown box (based off of data > validation > list) and produce values ranging from 0-40. I want a 4th column, pass/fail, to populate with "Pass" if ANY of the 3 fire columns produce a value of 23 or greater. Also, I need the 5th column to populate with either marksman, sharpshooter, or expert based on score ranges in the 3 fire columns, BUT sharpshooter and expert can only populate if the established score range is in the first fire column - otherwise they just get marksman. So let's say you have fire 1 = 22, fire 2 = 32, fire 3 = blank for shooter 1. Shooter 2 has fire 1 = 32, fire 2 = blank, fire 3 = blank. Column 4 for both shooters should read pass, but column 5 should read marksman for shooter 1 and sharpshooter for shooter 2.

2. Having trouble with something simpler - deselecting the first box in a column when applying formatting. I don't want to have to drag down and manually select an arbitrary crapload of columns, I just want to be able to label a column and THEN apply the formatting. Command + click doesn't do it, shift + click doesn't do it, alt + click doesn't do it. I know this one's not that hard but I'm drawing a blank and feel like an idiot.

3. Hiding values for the list so they don't show up just from scrolling over laterally in the spreadsheet. I guess I could just move them to a separate tabbed page? Right now I just moved them over beyond where the populated data will be with a couple columns of space in between.

4. Other conditional formatting - I need to be able to do basic "if, then" type stuff for multiple columns. So if Unit = 152 is selected, I want it to auto populate the UIC column with a certain alphabetic/numerical value (for example, WZ6CAB) and also populate weapon column (M16 vs M4). Also would like to color cells based on pass/fail, but that's polish - need the functional stuff first.

[atc4usmc]

Man I feel your pain! A few years ago (1999) I created an excel spreadsheet similar to compute PFT scores and populate cells with score and class (1st, 2nd, 3rd). What was really the kicker was having the formulate check a certain cell to see if it was an M or F (male or female) as this would generate different scores etc.

PM me your email address and I will send you a copy of this sheet and the formulas in there may help your progress for the above post. BTW, Im interested in the sheet for range quals if you are willing to share with the rest of us in the end.

[NorCalAthlete]

Sure, I can post it up along with the formulas for the cells. Got a few different ways to make it work right now from other friends but I'm still trying to tweak it because it's not quite exactly what I want to do.

[drdarrin@sbcglobal.net]

Lots of requirements in this list. I do a lot of work in Excel for my job and would be happy to help. I'm located in Sac. Hopefully you are too. If not, can you send me a copy of your spreadsheet and a phone number where you can be contacted. My email address is my username.

[DolphinFan]

Try

http://www.excelforum.com/

or

http://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

I would start with Microsoft developers center

[drdarrin@sbcglobal.net]

I emailed you what I think are the formulas you need for columns H & I. They are...

=IF(OR(E2>=23,F2>=23,G2>=23),"Pass","Fail")

=IF(E2>=36,"Expert",IF(E2>=30,"Sharpshooter",IF(OR (E2>=23,F2>=23,G2>=23),"Marksman","")))

Respectively

[drdarrin@sbcglobal.net]

Creating a spreadsheet for range quals and want to do a few things.

1. Having trouble with chaining conditional formatting/data validation lists. I have 3 fire table columns that populate via dropdown box (based off of data > validation > list) and produce values ranging from 0-40. I want a 4th column, pass/fail, to populate with "Pass" if ANY of the 3 fire columns produce a value of 23 or greater. Also, I need the 5th column to populate with either marksman, sharpshooter, or expert based on score ranges in the 3 fire columns, BUT sharpshooter and expert can only populate if the established score range is in the first fire column - otherwise they just get marksman. So let's say you have fire 1 = 22, fire 2 = 32, fire 3 = blank for shooter 1. Shooter 2 has fire 1 = 32, fire 2 = blank, fire 3 = blank. Column 4 for both shooters should read pass, but column 5 should read marksman for shooter 1 and sharpshooter for shooter 2.

2. Having trouble with something simpler - deselecting the first box in a column when applying formatting. I don't want to have to drag down and manually select an arbitrary crapload of columns, I just want to be able to label a column and THEN apply the formatting. Command + click doesn't do it, shift + click doesn't do it, alt + click doesn't do it. I know this one's not that hard but I'm drawing a blank and feel like an idiot.

3. Hiding values for the list so they don't show up just from scrolling over laterally in the spreadsheet. I guess I could just move them to a separate tabbed page? Right now I just moved them over beyond where the populated data will be with a couple columns of space in between.

4. Other conditional formatting - I need to be able to do basic "if, then" type stuff for multiple columns. So if Unit = 152 is selected, I want it to auto populate the UIC column with a certain alphabetic/numerical value (for example, WZ6CAB) and also populate weapon column (M16 vs M4). Also would like to color cells based on pass/fail, but that's polish - need the functional stuff first.

1) I think I have provided the formulas you need for this item.

2) as you have already discovered, conditional formatting is not quite that easy, but it's doable. I need more details to help.

3) I strongly recommend you move any reference data to a separate worksheet. It's much easier to manage. To my knowledge, there is no limit to the number of worksheets (sheet1, sheet2, etc) that you can have in a workbook. That also enables you to name each worksheet appropriately.

4) if there is a 1 to 1 relationship between the unit # and the code you wish to select, you can set up the data in a table and use the Vlookup function to search for the value (152) in the first column and return the value (WZ6CAB) in the second column of the same row. The list will have to be sorted on the values in the first column.

For example, enter 152 in Cell A1 and WZ6CAB in B1.
Enter as many rows as you need using the above format.
Highlight cells A1:B? and select Formulas, Define Name and assign the group of cells a table name like Table_Unit_UICs.
in cell A2, select a unit number.
In cell B2, use the formula =vlookup(A2,Table_Unit_UICs,2,false)

Should you need to add more rows to the Table_Unit_UICs, insert blank rows between row 2 and the last row of the existing table.
Enter the additional data you need.
Delete any extra blank rows.
Select the table (enter Table_Unit_UICs in the cell reference box, right under the Home menu option)
Sort the data by column A

Hope that helps.