Posts by Cronina

  • Number of days between two dates depending on which cell has start date

    I am in Column F (Variance) and have tried nested IF statements, but can't get them to work.


    Column B has a Baseline Date
    Column C is used in case the Revision Date
    Column D is used if the Revision2 Date.
    Column E is the Actual date the job was completed.


    If Column D is blank, then I calculate the Actual date - Revision Date (E-C)
    If Column C is blank, then I calculate the Actual date - Baseline date (E-B)
    If none of these are true it means that all dates are filled in so I calculate Actual date-Revision2 Date (E-D)


    I looked in the forum, but some of the answers are above my head. I hope you can help me with a formula.


    This is what I have so far: '=IF(ISBLANK(D3),0,IF(ISBLANK(C)E3-c3,E3-B3))


    Thank you for your help.

  • How to prevent date return of "1/01/1900"

    I'm trying to calculate baselines and variances to those baselines but I don't know VB and I'm struggling with a very bad formula: I'm in column K55 and this is the formula
    =IF(ISBLANK(F55)=TRUE,E55,IF(ISBLANK(G55)=TRUE,F55,IF(ISBLANK(H55)=TRUE,G55,H55)))


    What this is doing is:


    If column E55 has a date (orignial baseline date) then this becomes the approved date in Column K55
    However, there may be up to 3 revision dates in columns F55, or G55 or H55
    If there is a date in F55 then that one becomes the approred date in column K55
    If there is a date in G55 then that one becomes the approved date in column K55
    if there is a date in H55 then that one becomes the approved date in column K55


    The problem is that is that NONE of the dates are filled in the blank in F55 shows up as 1/01/1900 is K55


    Can you help me figure out how to return a blank of there are no dates anywhere (no baselines yet)


    If you can help me clean up the formula that would be an extraordinary.


    Thank you!


    Ana Cronin

  • Color Cells Based On Single Letter In Cell

    Re: Color Cells Based On Single Letter In Cell


    Great Thank you. Just one more question.


    Now that I have the ability to turn the cells Red or Yellow,
    How can I turn them Green if there are NO Red and NO Yellows on the indicated cells?


    Like this?
    =AND(ISERROR(SEARCH("R",UPPER(B3&B8&B12&B17))),SEARCH("Y",UPPER(B3&B8&B12&B17))SEARCH("G",UPPER(B3&B8&B12&B17)))

  • Color Cells Based On Single Letter In Cell

    I have an excell spreadsheet were users enter a letter on a cell corresponding to the status of their projects. If their project in Red they enter R, if Yellow, then enter Y, and Green G.


    This information is trasfered to other sheets and eventually end up in a master Dashboard.
    What I would like to figure out is how to turn the master cells in the dashboard the color depending on the letter AND also enter the text R or Y or G.


    The logic is this:


    1. If there are any Red's on the sheet then the dashboard turns Red
    2. If there no Red's but there are Yellow's, then the Dashboard turns Yellow.
    3. If there are no Red's or Yellow's then the dashboard turn Green.


    We are changing the colors manually now, but I've been trying to come up with a better way.


    So far entering the letters is working with this formula:


    =IF(B3="r","R",IF(B8="r","R",IF(B12="r","R",IF(B17="r","R",IF(B3="Y","Y",IF(B8="Y","Y",IF(B12="Y","Y",IF(B17="Y","Y","G"))))))))


    I have a conditional formating formula to turn the cell red.


    =OR($B3="R",$B8="R",$B12="R",$B17="R")


    But now how do I tell it that it should be Yellow if there are no red's but there are yellow's on the cell?


    Any help would be appreciated.


    Thank you.

  • Sum By Criteria With Filters

    I have a spreadsheet with a summary on the first 3 rows. I am using a "sumif" formula, but do not know how to get it to sum the totals when I click on the filter downarrow.
    This is what I have:
    =SUMIF($A$10:$A$52,"2007",(D10:D52))
    I tried this =SUMIF(9,$A$10:$A$52,"2007",(D10:D52)) but it did not work.

  • Multi Criteria Counting

    Im trying to construct a nested Countif statement.


    I need to count the number of instances that "Project" appears in Column O AND "TS" in Column N.


    The range is in another in Sheet2. and the summary in Sheet 1 where I want to have the Countif(AND...??? statement


    Example Counif(Sheet 1 Column 0 contains "Project" AND if Column N Contains "TS"


    Your help would be appreciated since I have spend 1 hour trying to figure it out.

  • Subtotal After Filtering

    I have a large spreadsheet that I need to subtotal, but need to only see filtered data.


    Example: Data contains several rows for January, February, March etc...


    I have placed data filters, but need to see the subtotals each time I view a 'month' of data.


    Like subtotals, but only filtered data.


    How can I accomplish this?


    Your help would be very much appreciated.

  • Remove Blanks From List

    I have the list of names ready...but now I need to know how to take out blanks from the column.
    Example: I have a name in Row 2, but the next name is in Row 5 and the Next in Row 9. etc. How can I get a neet list in Rows 2,3,4....

  • Set-up Of IF AND OR Formula

    Re: Set-up Of "if" "or" Formula?


    I'm going to use the Vlookup rather than trying to figure out the structure of this "If" "or" formula.


    I believe the Vlookup will work.


    Thank you, you can close this post

  • Set-up Of IF AND OR Formula

    Re: Set-up Of "if" "or" Formula?


    This is a one time formula and it is all in the same line.
    What I need is to build a list in column G with the names of those people that have not completed their assignement "completed"
    The names are in columns A, C and E and the word "completed" or blank" in columns B, D, F

  • Set-up Of IF AND OR Formula

    =IF((B2<>"completed",G2=B2," ") or (C2<>"completed",G2=D2," ") or (E2<>"completed",F2=D2," "))


    This is what I would like to do, but I do not remember the best to handle an "If" "Or" formula.


    I need to pull names from a column if the column to the right does not have the word "completed". I have three columns of names.
    Column A - Name and Column B "completed" or Blank"
    Column C - Name and Column D "Completed" or Blank"
    Column E - Name and Column F "completed or Blank"

  • Open a "Note" subsection from Cell.

    I have an excel spreadsheet with a list of job candidates. We are interviewing about 10 - 15 per month and I can't keep everyone straing as to what was said about them. My goal is to create a column where If "clicked" it will open a sub section for Manager's comments about the candidate.


    Is there a way I can do this? and how.


    I know a bit of Visual Basic, enought to ajust it to my needs, however I don't know how to program from scratch.


    If I have a excel spreadsheet with say:
    Candidate ID, Name, Job Description, Interview Date, Evaluation/Comments.


    How can I make the Evaluation/Comments visibile so that it can be read when the candidate is selected.


    I hope you can help

  • Insert a deffinition in a word, hover over

    Re: Insert a deffinition in a word, hover over


    Yes this works. I guess the problem I was having was with PowerPoint. When data was cut and pasted into the PowerPoint from a word Document the comments were lost.


    Do you know of a way to keep comments within words in PowerPoint?

  • Break report into many reports

    Does Access have the ability to take a report with many pages and create a different report for each page?


    Say I have a report with 100 pages and I want to create a separate file for each page, can this be done using visual basic? Can visual basic assign report names?