Excel Programming
Results 1 to 10 of 10

Thread: Excel Programming

  1. #1
    AO Security for Non-Geeks tonybradley's Avatar
    Join Date
    Aug 2002
    Posts
    830

    Excel Programming

    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

  2. #2
    Senior Member tampabay420's Avatar
    Join Date
    Aug 2002
    Posts
    953
    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!
    yeah, I\'m gonna need that by friday...

  3. #3
    Ninja Code Monkey
    Join Date
    Nov 2001
    Location
    Washington State
    Posts
    1,027
    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.
    "When I get a little money I buy books; and if any is left I buy food and clothes." - Erasmus
    "There is no programming language, no matter how structured, that will prevent programmers from writing bad programs." - L. Flon
    "Mischief my ass, you are an unethical moron." - chsh
    Blog of X

  4. #4
    Senior Member tampabay420's Avatar
    Join Date
    Aug 2002
    Posts
    953
    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?
    yeah, I\'m gonna need that by friday...

  5. #5
    AO Security for Non-Geeks tonybradley's Avatar
    Join Date
    Aug 2002
    Posts
    830
    Just me. The spreadsheet will be distributed after I get the summaries for the columns completed.

  6. #6
    Senior Member tampabay420's Avatar
    Join Date
    Aug 2002
    Posts
    953
    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...
    yeah, I\'m gonna need that by friday...

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

    if A1, B1, C1 = Y then print true else print false
    http://www.danasoft.com/sig/c0bra.jpg
    click here to hack my computer and delete all my important files

  8. #8
    Senior Member
    Join Date
    Oct 2001
    Posts
    748
    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.

  9. #9
    The Iceman Cometh
    Join Date
    Aug 2001
    Posts
    1,209
    Try this for the first one:

    Code:
    =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:

    Code:
    =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

  10. #10
    AO Security for Non-Geeks tonybradley's Avatar
    Join Date
    Aug 2002
    Posts
    830
    Thanks for all the help guys!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •