# Posts by chimp

• ## Condition formatting - if 01/01/1900 replace with blank

Re: Condition formatting - if 01/01/1900 replace with blank

cheers.. perfect

• ## Condition formatting - if 01/01/1900 replace with blank

Hi,

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...

Thanks

Andy

• ## Condition Format Column Chart based on pass/fail citeria

I have a spreadsheet with some very simple data in that i am trying to chart.

I am trying to automate the bars colours based on the results of a cell

Attached is the spreadsheet as an example... top graph is the linked data and the bottom is what i am trying to replicate..

Thanks

Andy

• ## if formula with Date + specific sum

Hi,

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

Example

Pass date Refresh period (years) Expected Result

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

Tried formula

=IF(R4=1,"Q4+365",IF(R4=2,"q4+730","r4+1825"))

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

Andy

• ## calculate days or nights shift

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...

• ## Compare values and calculate pecentage of change

Re: percentage of

thats what i calculated too but my mind was just saying "NO"... I can't start to explain how bad my day has been

Thanks

Chimp

• ## Compare values and calculate pecentage of change

I have a simple problem that i can't get my head around.

I am trying to display how much i have reduced accidents by.

2010 - 116 accidents
2011 - 83 Accidents

i think i am just over tired cant seem to get it rightforum.ozgrid.com/index.php?attachment/43128/

• ## vlookup problem, if statement

Re: vlookup problem, if statement

forum.ozgrid.com/index.php?attachment/42191/

In the strippeddown example you will see the problem is in Row k12 the figure displays 17.0 whereas as it should actually be zero given the employee has worked less than 1 year.

I therefore need this to read zero, else do the lookup

• ## vlookup problem, if statement

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

• ## vlookup problem, if statement

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?

• ## vlookup problem, if statement

Hi,

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, "=vlookup...work your magic, however if the f8 result=17 then "0")

hope that makes some sense

Andy

• ## Find and replace problem

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

Example

01.01.01
01.01.02
01.01.03
Etc

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

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

01-01-01
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

• ## adding "-" character to string of numbers

Re: adding &quot;-&quot; character to string of numbers

PERFECT!!

Thanks

• ## adding "-" character to string of numbers

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

Andy

• ## Create Team Fixtures Table

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

Andy

• ## Create Team Fixtures Table

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...

Rules:

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.

• ## Select a Combobox - display results in 2 others

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

Example...

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 :

http://www.utteraccess.com/for…lapsed&sb=5&o=&PHPSESSID=

• ## Current year + 3 years

Re: Current year + 3 years

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

can i ammend the formula :

=DATE(YEAR(B5)+3,MONTH(B5),DAY(B5))

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

cheers

Andy

• ## Current year + 3 years

Re: Current year + 3 years

i have done that but as you will see from my example attached....

the cell turns red, even though the retest date is 2007

this particular cell should not turn red until 15/03/2007

Cheers

Andy

• ## Current year + 3 years

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

Example

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

Andy