Question re Combo Box & vlookup within Excel

  oo7juk 21:39 10 May 2007
Locked

Hi,

I'm try to create a employee training file but, require some assistance.

I have a workbook containing 4 sheets. Each sheet contains employee names, dates and types of training completed. I want to create another sheet and incorparate a combo box so that I can select an individual employee and hopefully display all the training for employee. The question is can you link the other sheets when creating the combo box? Also want to add extra column next to the dates so that it flags up when employee is due refresher e.g. 3 months prior, maybe coloured Red or something similar.

Many thanks.

  silverous 09:19 11 May 2007

I'm sure this can be done but I'm a bit unclear - if you picked Joe Bloggs and he was on sheets 1 and 3 how would you show both sheets when he was selected?

Or do you want the results summarised somewhere on the on the combo box sheet?

Do you want the extra column to be on the invidiual sheets or in the results when you search?

Presumably the combo box should contain a unique list of employees from the 4 sheets? i.e. if joe bloggs is on sheet 1 and sheet 3 he should appear in the combo box but only once.

In terms of flagging up the dates, presumably the date is a "Refresher Due" and if that is within 3 months of "today" then it is flagged? You don't necessarily need an additional column, you could use conditional formatting to highlight any cells in the relevant rows e.g. assuming your date is in the B2 cell you could add conditional formatting with a formula of:

=AND(B2 <= DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())),B2>=TODAY())

Come back on the bits and pieces above and I'll help you out.

  oo7juk 16:36 11 May 2007

Thanks silverous,

Encountered a problem re dates. On my results sheet I have a column with the completed dates and the vlookup is working ok in relation to referencing other sheet and populating with date. Unfortunately, the cells which are blank i.e. no date, the cells within the results page are all displaying (00/01/1900) minus the brackets. If an employee hasn't completed the trainng then the sheet 'recycling' remains blank.

For info the cells are formatted with the category - date and the type is the top selection.

Cheers,

  silverous 16:42 11 May 2007

You don't seem to have answered any of my questions - are you skipping doing that stuff now?

I'm not 100% clear on your spreadsheet from your description, but I think I know the problem you are having as I've just reproduced it. It can't display a blank date so displays what you are seeing.

If you format the date cell with a custom format of say:

dd/mm/yyyy;;

(go into Format, Cells, then choose Custom and enter the above)

What you are doing there is saying display empty results as blank.... was a longshot but it appears to work?

  oo7juk 00:38 12 May 2007

Thanks for the info re dates and apologies for skipping a few stages. At the moment I'm at the following stage. I have my workbook containing 4 sheets, may results page contains my combo box and I'm using the vlookup formula to populate my cells within the results sheet. Column 3 (results sheet) contains my vlookup formula so in order for me to get my 3 month trigger will I have to create a new column because if I don't I will be overwriting my vlookup formula.

Many thanks,

  silverous 11:31 14 May 2007

Depends, if you want your trigger to display something 'separate' then yes it should be in another cell. If you just want it to highlight then conditional formatting can do that on the result of the vlookup, you just apply the conditional format to the cell you want to be highlighted.

  oo7juk 12:25 14 May 2007

silverous,

I have created another column'(D)' for refresher dates and it has the following formula -

=IF(ISTEXT(C2),"",IF(C2<>0,DATE(YEAR(C2)+1,MONTH(C2),DAY(C2)),""))

If cell 'c2' has a date of 14/05/2007 then cell d2 is populated with 14/05/2008, that's what I want but, for some reason some of the cells in col 'D' have not populated and the only way they populate is if I go into my sheets and re-type the date.

Thanks,

  silverous 13:09 14 May 2007

The only way I can see the D column not being populated is if the date in C is invalid in those cases e.g. 31/04/200x is not a valid date.

Can you give a couple of examples ?

  silverous 13:11 14 May 2007

Ah, hang on - if they then populate when you retype the exact same date into your source sheet then that is something else....

Could it be the format on the source data sheet e.g. it thinks the date is actually text rather than a date?

Find the dates causing a problem and ensure they are formatted the same as the ones that are working.

I presume the date in column C is the vlookup result.

  silverous 13:12 14 May 2007

As well as formatting check they don't have any other characters in them.

I just reproduced this by entering:

'30/01/2006 (i.e. a text string) into the data being vlook'd up.

  VoG II 13:20 14 May 2007

Just to butt in...

Select all of your dates, Data > Text to Columns, click the Next button twice to take you to the third screen, in Column Data format tick Date, select DMY then click Finish.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

11 best portfolio websites for designers and artists

Office for Mac buying guide: Price, Office 2017 rumours & new features

Comment désactiver les programmes qui s'exécutent au démarrage de Windows 10 ?