Excel Programming

[tonybradley]

This isn't security related per se, but I was hoping some of you programming geniuses out there might be able to help me out with the logic or syntax to accomplish what I need in Excel XP.

I have columns of data. I basically need to write a formula that will assess the entire column and return a true/ false.

For example- one column contains Y for yes and N for no. I need a formula to determine if all of the cells are Y. If any of the cells are N then the result of the formula should be false.

I also have a column of dates. I need a formula that will analyze each of the cells and if ANY of the cells have a date more than 1 year ago then the result of the formula should be false.

In the end, I need these summary cells for each column to be green for true and red for false. I was planning on simply returning true/false and manually filling the cell color based on the formula result, but if anyone knows how to automatically fill the cell color based on the formula result that would be helpful too.

TIA

[tampabay420]

yeah, and while you're at it, why don't you finish my Compensation Plans... (in excel...)

LOL- I'm just messin' around...

Tony: PM me a sample sheet/data!

[Juridian]

By installing WSH you get sample scripts dealing with excel via .vbs and .js . You might find these helpful, just do a search for the file extensions and you should be able to pick them out by the name. WSH is prolly already installed on your machine.

[tampabay420]

hey, if you don't mind using Macro(s) ...
this would only take a few minutes...

Is this tool for you or will it have multiple users?

[tonybradley]

Just me. The spreadsheet will be distributed after I get the summaries for the columns completed.

[tampabay420]

well, here is the situation... I can easily do this using VBA macros... If not, i will have to look for some way to do this with Excel Formulas...

[c0bra]

mmm... can't you use something along the lines of

if A1, B1, C1 = Y then print true else print false

[mohaughn]

Tony- You can easily do this using just the equation builder and conditional formatting in excel.

Under the Format dropdown menu is an option called conditional formatting. In there you can easily say, if the field ="true" then set the font color to red. Just setup two conditional formats one for blue, one for red.

Just found a very weird issue with excel.

Here is the proper equation, =IF(A1:A9="N","False","True")

Of course you will need to change the A1:A9 to match up with the column numbers in your spreadsheet.

The weird thing is that if you place this equation into the same column that you are trying to evaluate it gives you a #value! error. Even if you were to change the equation to use absolutes, =IF($A1:$A9="N","False","True") it still doesn't work. Not sure why. This is in excel2k

So you just have to make sure that the evaulation equation lives in a different column. If this is a problem you could create another column, place the equation in it and hide it, and then use a copy function to move the output to the proper column for formatting.

[avdven]

Try this for the first one:

=IF(COUNTIF(A1:A9,"N")>0,FALSE, TRUE)

It'll work regardless of where you put it.

Edit: Here's something for the second one:

=IF(COUNTIF(B1:B9,"<10/16/2002")>0,FALSE,TRUE)

The only problem is, you have to hard code the date, since the countif condition can't reference a cell. If it could, or if you want to try something else, instead of the hardcoded date, you can use today()-365

And for the coloring, look up "Conditional Formatting" in the help. If you want an example spreadsheet, I can send you one.

If you need other help, PM me.

AJ

[tonybradley]

Thanks for all the help guys!