I am currently working on a rather large multi-week planning for a project I
am running and on every page I have apart from the students the name of
teacher listed. Now what I wanted (on a seperate page) is to have excel look
for the amount of times a teacher's name is present on each and every sheet,
count them and display them. So far so good as that wasn't to hard.

But now to the problem. Since I am referring to another sheet, I can't just
copy through the formula without having to change the name of the SHEET in
every single cell of the top row. So I thought I would use the cell + text
combination method to see if that works and have Excel work for me, instead
of the other way around.

In a formula, the reference to a different sheet is for example displayed
as: 'W37'!

Now what I tried to do is to leave the 'W' standing and have the ROW (as
seen below) determine what number it should be combined with. So for example
by making a reference to cell C1 which has the value 37, leaving you with the
combination W37. Unfortunately trying to use the & placed within "" won't
work and I am rather clueless at this point on how to solve this. Does anyone
have any suggestions on how to combine this to make it work?

Row: 37 | 38 | 39 | 40 | 41 | 42 | 43 | 44 | 45
Sheets: W37 | W38 | W39 | W40 | W41 | W42 | W43 | W44 | W45

(note: there are a lot more weeks, the above is just to get the idea)

Thank you in advance.

Mark