I really need help using Excel, can anyone help me?

Question

I have been asked by my work to develop a spreadsheet that will enable us to see who has what holiday booked off. I have created a simple table with dates across the top and names down the side.

What they want is to click on a particular date (e.g.1st Jan 09), and see who has booked that day off and the amount of hours booked off.

Can anyone help me?

Thanks in advance.

Answer 1

Hi there,



First of all, I assume that given Excel only goes up to column 'IV' that the column headings don't have every date on it... That would then mean that you can't do this in the way you are thinking...



This would therefore be the really simple way of doing it...



Try reversing the columns/rows and then try this...



In cell A2 type the first date of the holiday year. (e.g., 01/01/2008)



Then in cell A3 type the following formula:



"=IF(WEEKDAY(A2+1)=6,A2+3,A2+1)"



If your company works 6 days a week instead of 5 then replace the 6 with a 7 and the A2+3 with A2+2.



Then copy and paste this formula across all rows until all possible holiday dates are shown.



Then type the names of people in the columns.



Select cell B2 and click Window->Freeze Panes



Then click Format->Conditional Formatting



Change Condition 1 to 'Formula is' and then type =B2=1 in the box. Set the format to have a different background colour such as yellow which will make the booking stand out. Then copy and paste this cell to all possible ranges of the holiday table...



Then if a person books a day off as holiday then enter a '1' in that persons name/date cell.



Once you have done this then highlight all the cells from A1 down to the end of the date list and click Data->Filter->Autofilter.



Once you have done this you can then use the 'Autofilter' to select a date or range of dates to display the holidays for...



Example: Choose [custom] from the drop down menu and then the first condition is 'greater than or equal to' and type 01/01/2008 and the second condition is 'less than' and type 01/02/2008 then click 'OK'. You will now see all the holiday bookings for January 2008!



Of course there are far more intricate ways of doing this that are far less crude (using Visual Basic programming for applications) but they couldn't be explained here on Y!A in simple detail...



Perhaps you could try the Microsoft Office sample files (templates) to see if there is something suitable that you can adapt instead:



http://office.microsoft.com/en-gb/templates/CT101527321033.aspx?av=ZXL



Good luck!

Answer 2

This is actually very easy.



Learn VBA.



Add a macro that is trigged by clicking on the calendar. Search the spreadsheet to see who is marked down for that date, then display a list.

Answer 3

do you have microsoft access??



its heaps easier than excel for doing these kinds of things because you can run queries.

if you dont have it ask your work if they can get it because it really is an essencial piece of software.



queries are easy to do and any tutorial on the internet will be able to tell you how to do it

Answer 4

Attend a computer course. Have a look at the link below.......