excel help needed

[AHarvey]

Hi all,

Every week we do assessments on employee's, this is all done via excel and we then have a master spreadsheet to pull the info together from each month/week.

For instance the folder layout is like this:

Master Spreadsheet

Folder = May
week 1 spreadsheet
week 2 spreadsheet
week 3 spreadhseet
week 2 spreadsheet
Folder = June
week 1 spreadsheet
week 2 spreadsheet
week 3 spreadsheet
week 4 spreadsheet

Then, after each week I have to go into the master and link each and every cell up for all the employee's which takes me a good 40 mins and is boring.

Each week is a different row, with employee's across the top.

When I link them you end up with this in the cells:

='Z:\SUN BINGO MANAGEMENT\Management Meeting Files\Chat\Quality Chat Monitoring\June 09\[WC 5.6.09 - 11.6.09.xlsx]Assessment Summary'!$C$9

I then drag that across the cells to link them all to that spreadsheet, then I have to go through and manually change the $C$9 to $D$9, then $E$9 incrementing the letter each time.

Nearly there now

Is there anyway I can just put that in to first cell and have a formula in the others that goes through the link in the cell before and increment the letter by one? something that runs through the string, hits the $ symbol and then changes the letter to it right?

 

[AHarvey]

Sounds like you want a macro, but I don't have enough experience with VBA to offer more assistance than that really. And I presume you mean employees not employee's.. once I could've coped with, but by the third time I was grimacing

What makes me grimace is people like you that have to try and pick on people's spelling and grammar to make yourself feel better about your life! I am sure you wouldn't have the balls to try and correct someone in real life!

As that post was written from my phone and it automatically changes what you have written to what it thinks is correct it doesn't surprise me that there are a few errors. I normally have the time to double check, however in this case I didn't.