Newbie needs help with VLOOKUP

Ok, so I just introduced myself in the forums and here I am pleading for help with my first excel problem. I will do my best to describe my problem.

I created a spreadsheet to track attendance for 30 people for the year. I made a sheet for each month of the year with each employee's name down the first column alphabetically, and the days of the month across the top. Info regarding which type of leave is being used (vacation, sick, etc) is entered in the appropriate cell by way of a drop down menu. So there are 12 sheets (1 for each month) for entering info, then there's a sheet for each employee. These employee sheets have the months of the year down the first column and each day of the month going across. The cells in these employee sheets grab the info that is entered in each month sheet.

Here's my problem:

I originally accomplished grabbing the info with an IF formula. This worked fine but when I needed to remove a name or add names, I had to redo every employee sheet that was affected by the change. The the IF formula was grabbing info according to a specific location, so when a name was removed and the other names shifted up or down as a result, the employee sheet was now grabbing from a different employee.

I tried to fix this issue by redoing the employee sheets and changing the IF formula to a VLOOKUP formula. This seems to accomplish what I need but when I create the first VLOOKUP formula and try to drag it across the row, the table array changes incrementally and the column index number stays the same. I need the column index number to change incrementally, and I need the table array to remain the same.

The VLOOKUP formula I am using is: =VLOOKUP("Smith",January!A6:AG39,5,FALSE)
when I try to drag this formula across it changes to: =VLOOKUP("Smith",January!B6:AH39,5,FALSE)

I need it to change to: =VLOOKUP("Smith",January!A6:AG39,6,FALSE)
then the next one to: =VLOOKUP("Smith",January!A6:AG39,7,FALSE) and so on.

Is there a way to tweak the VLOOKUP formula to accomplish this, or is there another formula that someone could suggest? Just bear in mind that I'm a newbie and get confused very easily.

Thanks in advance.

Thanks for the quick reply! I'm gonna give it a shot now. It takes me some time to figure this stuff out so I'll let you know how it works as soon as I can. Thanks again.

Pepe you are AWESOME! It works but created a different issue. Now if there's no info in the cell to grab, your formula returns a 0. I need it to stay blank. Is that easy to fix?

Oh, and oeldere thank you also for replying but I'm such a noob I don't know how to do macros and pivot tables.

Worked like a charm FDibbins! Thanks to you and Pepe I'll have this attendance spreadsheet up and running by the new year. You Excel Gurus are AWESOME!