Content cell to determine which sheet to look at

[Mark]

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

 

[MartinW]

Hi Mark,

With 37 in C1 38 in D1 etc.
=INDIRECT("W"&C1&"!A1") in C2 and dragged across
will return A1 from sheet W37, W38 etc.

Is that the sort of thing you are looking for?

HTH
Martin

 

[Mark]

Hi Martin,

Thanks for the quick response and thank you for providing me with a solution
to my problem. It worked like a charm. I really appreciate it.

Thank you.

Mark

 

[MartinW]

Glad I could help, Mark.

Hi Martin,

Thanks for the quick response and thank you for providing me with a
solution
to my problem. It worked like a charm. I really appreciate it.

Thank you.

Mark