Any excel experts able to help please?

TraceyJ_2

Hi, I would really appreciate some help with conditional formatting and pulling through data (where rows are added/removed periodically) from one sheet to other sheets if there are any excel experts here?

I've anonymised the data here but it's basically a spreadsheet for monitoring appraisals and training for approx 100 staff.

I would like to be able to have a list of staff on the first sheet which when updated (by adding new rows for new staff) automatically pulls through to the other sheets. So far I've only been able to pull through data from a certain number of rows and when I added a new row that didn't pull through to the other sheets.

On the appraisal sheet I have set up some conditional formatting which isn't always working but I can't see what's wrong with the formulae

I'd also like the training sheet to have conditional formatting based on training dates/requirements but until the appraisal sheet is working properly I haven't added this information.

The final sheet is a summary but isn't working properly yet as I need to think about what I want that to show.

I hope this link works...

https://www.dropbox.com/s/s61oiz9gfmfgwbl/APPRAISAL%20AND%20TRAINING%20MONITORING%20ANON%20for%20sharing.xlsx

I would really appreciate some assistance if there are any excel experts who have some time to help.

Thanks very much
Tracey

LucianH

My first thought is it could be difficult without using macros.

You're effectively pulling together a database and it's best to have one sheet containing the "master data" where all information is contained for both the appraisals and training. The problem that this will leave is how then best to present the information for reporting. Two possible ways i can think of:

1) Reasonably simply print macros which will hide the unwanted columns, print sheet then unhide the columns.

2) Possibly use pivot tables that work on the "master data". Not sure how (or if) conditional formating works with pivot tables though.

One question though, you obviously will have the same number of rows (entries) for both the list of staff and the appraisals. Will you have the same number of entries for the training - ie, could an employee have more than one training entry?

Not sure about your conditional format problem. Can you identify on the sheet where's it not working and what you're expecting to see.

TraceyJ_2

Many thanks for reading my post LucianH.

One sheet would be fine. I don't know anything about macros though but I can read up and learn fairly quickly if this is what I need to do!

Re your question, there will be about 7 entries for different types of training. I thought if someone helped me with one type of training then I would be ok with adding the rest.

Re your conditional formatting question, some of the boxes in column E of the second sheet are not coloured as I would expect them to be? They are just white boxes.

Thanks for your help.
Tracey

Dee123_2

Hi Tracey,

Regarding the missing conditional formatting, only two conditional formats are set up in Column E (the purple and the amber). This seems correct as your legend at the top indicates you are only looking for formatting on dates either appraisal or review overdue or appraisal or review due in next 2 months. The white ones are dates more than 2 months in the future so they fall into neither category. What else were you looking to add?

With the pulling through of new rules, I don't mean to send you down the wrong path as I couldn't get it to work myself, but this thread seems to be the same topic with a solution that works for the OP:

http://www.excelbanter.com/showthread.php?t=260482

Might not work but may save you going down the macro route.

Dee

TraceyJ_2

Thank you Dee, I hadn't realised there were only two colours expected in that second column! Thanks for the link too, I will have a read.

bazster

Sounds like a rudimentary database. Would be an absolute doddle in Access, about 10 minutes' work.

chris-j

Strikes me that for what you are wanting to do, you are adding a staff list sheet for the sake of it and it adds no value.
Have the staff listed on the appraisal sheet, on training sheet do a formula reference to the cells on appraisal sheet (as data is the same) and if you add new staff, just put them on the appraisal sheet then drag down cells on training sheet as it will pick up the formulas. for training either have separate sheet for each course or, if only six or seven have them listed in additional columns.

If you want a way to look up a particlar staff members record you could add an extra column on appraisal and training sheets (and hide it) which concatenates surname and forename (and department if there are duplicates) then add a new sheet where you enter staff name (and department if used) and use vlookup formula to gather together all the data relating to that person.