Any Exel Gurus out there?

Discussion in 'Gaming and Software' started by Jerrycan2793, Apr 18, 2011.

Welcome to the Army Rumour Service, ARRSE

The UK's largest and busiest UNofficial military website.

The heart of the site is the forum area, including:

  1. Im trying to create a spreadsheet which will show what training is required by my staff and when they are due a refresher (6 Months on from the training).

    Is there a way I can do the Spread sheet so that if for example

    Bloggs Completes x Training on Y date, That 5 Months on from Y Date That specific block containing the training title changes colour to Orange (To suggest he needs a refresher imminently) and after 6 Months on it changes Red to suggest he is dated?
  2. Conditional formatting is for you. Below is a link, because typing it all out on here would be murder.

    Conditional formatting Excel 2007: Learn with Examples | Excel 2010

    Depending what suite of Office you have, it's all about having conditions to change the format of a cell, be it colour, font, wording, etc.

    PM a basic sheet and some details and I'll knock it up for you
  3. You need to use conditional formatting in the format drop down, putting in two conditions based on “IF” statements changing the colour as necessary. Help is very useful here.

    PM me if you need any more assistance…

    Edit to add: beaten to it by FatCav. Good luck with it - it's very simple!
  4. This formula will place the time elapsed, in whole months between today and the date in cell A1 in whichever cell it is placed in.

    Unless I missed something in the conditional formatting menus!
  5. Righto, that's all just gone over my head if I do the sheet can one of you guys make the pretty colours come up?

    IOU's on pints will be issued if any of you will be at the Army Navy Match
  6. Jerrycan (and anyone else if they want it)

    Attached is a zipped .xlsx spreadsheet with some simple Condtional Formatting in it to colour the cells when the date's get within a certain time frame.
    RED>183 days (6 months) and ORANGE > 153 days(5months or so) and < 183 days. All the formatting is within the cells Conditional Formatting and not the date cell. Rememeber to copy the Course cells (column B) into another using: Paster Options > Formatting (R)

    If you want any other colours or date ranges, or if the zip doesn't work (don't usually use it), then please shout

    Attached Files:

  7. Thats fantastic, Easily edited to the purpose I require thankyou, I assume I can drag the conditional formatting down the columns to extend it etc...
  8. Sympathetic_Reaction

    Sympathetic_Reaction LE Book Reviewer

    it may not drag, can't remember. But it will definitely copy and paste. If you just want the conditional formatting then copy a cell with it in then select the cells you want to to paste to and select 'paste special' from the toolbar and use the 'format' option in that.

  9. Yes you willbe able to drag.
  10. Oi how come I'm doing all the work? (Actually, you IS de man, bro! I have learned more about excel in 30 minutes on here than at any other time. WTF where you when I was mapping the desert in Peru ... never mind. Thanks for bloody good advice - excel ent in fact)
  11. Thanks again, if your ever in the Hull area pop into my place of work for a free beverage on the management
  12. Although I'm not allowed to go to the AvN this year, can I nominate a Beer-by-proxy individual, as I would hate you to get away without paying for a pint (or two)?

    If so, ARRSEr's should send decent reasons why they should deserve a pint from Jerrycan on a post card to the usual address
  13. At AvN prices it will be a half... :p

    Quite happy for you to nominate a proxy though