Posts by chimp


    i have looked around the search option and am struggling to get my head around this...

    I am trying to format a cell that displays result of

    Exam date (Q3) x retest period in years(R3) = due date (=Q3+(R3*365.25))

    however if the exam date and retest date are blank, i am getting 01/01/1900 in the retest field

    I have worked out the 3 main formats of Red <today, Amber today >30, Green > 60 however want the 01/01/1900 cell to be black or blank if applicable...

    sorry for appearing stupid...but would really appreciate some help on this one...




    I am looking in a spreadsheet to add a specific number of days to a cell result based on another fields entry.


    Pass date Refresh period (years) Expected Result

    23/09/2013 1 23/09/2014
    01/07/2014 5 01/07/2019

    Tried formula


    I am getting the result of r4+1825 in the cell not the pass date plus 5 years...

    Please help


    I have a sheet that logs when orders are placed, i need to establish if they are on a day or a night shift.

    My shifts are:

    Days: 07:00 to 19:00
    nights: 19:00 to 07:00

    my raised field is in column N starting at N2

    the data is stored as follows:

    05/01/2009 12:55:00

    which would represent a day shift...

    my problem is that the night shifts start 5 hours into the previous day than it ends...

    can anyone help me with a formula that would cater for this...

    thanks in advance

    Re: vlookup problem, if statement

    ok i have tried the following....

    =IF(F8=0,0,VLOOKUP( WEEKS DUE'!$A$2:$AU$52,F8+1,FALSE),"Person under 17 or Less than 1 year")

    however i get an error arounf the 0,0,VLOOKUP area...

    Sorry for being a pain here

    Re: vlookup problem, if statement

    ok after much thought, what i need this formula to read is much simpler than my stupid original mindset....

    If f8 =0 then 0 else run vlookup, how do i type this please?


    I am using a lookup which works fine to a point....

    =VLOOKUP(J8;'Weeks Due'!A$2:AU$51;F8+1;FALSE)

    J8 referes to "Projected Age Years"
    F8 refers to "Projected Service Years"

    however i am getting some funny results if the "F8+1 = 17"

    this is all to do with some strange spreadsheet to calculate bonus payment, however you only get paid the bonus if more than one years service and over the age of 17...

    how can i modify the vlookup to read, " your magic, however if the f8 result=17 then "0")

    hope that makes some sense


    I have a column of data which is all in the same format :



    This relates to a storage location I have shelf/bin/divider

    The problem I have is I need to change the ". For -"

    And so on, I have tried the normal find and replace however the result then changes to a date form.

    I am happy to insert a column to add a formula to resolve this if needed, anyone help please.... This is driving me crazy

    I have alarge list of numbers that are in the wrong format for me to use in my search format.

    the list is as follows:

    Cell "A1" = 1234567890

    I need cell "B1" to display "1-234-56-789-0

    My numbers in Column A are alsways 10 digits

    and always adopt the same format as per B2 requirements, 1 digit, 3 digits, 2 digits, 3 digits, 1 digit seprated by a "-"

    can someone please advise how this can be achieved


    Re: Create Match List From Another Worksheet

    wow that was bloody quick.......thanks..........that sorts out part one of my multi chunk project...Thanks..

    i now need to assign some dates, and a few rules to my list...

    In my Spreadsheet i have now added a dates Worksheet..

    First Dates

    I need to assign a Fixture to a date where they fixture type is League.... not assigning dates to the dates that those that are described as Cup...

    i have had to add the dates sheet in a separate file due to file size...hope ya get the idea..

    Can you assist please?

    Thanks for ya help


    I need to create a full list of fixtures for a football (Soccer) league i am involved in.

    i believe this is a multi stage project that i need to tackle in bite size chunks.

    I have a list of teams, division that they play in and there home stadium name.

    my spreadsheet has 2 sheets visible at the mo...


    I need to create a list of all games that require to be played.

    All teams in the same division must play each other twice, once at the home stadium of each club.

    Only division 1 clubs play against other division1 clubs

    Only division 2 clubs play against other division2 clubs

    I need a full list of matches to be generated in my worksheet called "required Matches"

    can this be done via a macro? if so i really could do with some help please.

    i have a word document that i am trying to semi automate a few of the functions i have put on the page...


    In the attached document you will see i have three drop down boxes, which i believe could technically be reduced to just 1...

    this is where my problem starts.

    i want to be able to change JUST the salary drop down box and both the grade and the shift premium would then adjust automatically.

    Can this be done..

    the thing i am struggling with is where would i store the data to say that if the salary is £14,527 the Grade would be "A" and the shift premium would be "15%"

    please any help on this matter would be much appreciated.

    at the bottom of the attached document are a list of some of the grading structures: you will see that the unique item is the Salary! field

    also posted at :…lapsed&sb=5&o=&PHPSESSID=

    Re: Current year + 3 years

    ok i have sorted that now thanks, but have stumpled across another issue here....

    can i ammend the formula :


    so that if B5 is null then the cell D5 will be null please



    I am trying to get a cell to display the refresh date for retesting....


    Test 1 ----- Test date 15/10/02 ------ Retest Date ---- 15/10/05

    I have the first 2 bits and need a formula to calculate the additional 3 years for the retest date...

    i then need this to be formatted to change colour from a green cell to red colour 3 months before the test date, so in this case on the 15/7/05 the cell would change to Red.

    i assume this could be done via conditional formatting, but need some help please.

    Many Thanks