Content cell to determine which sheet to look at
 

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

Content cell to determine which sheet to look at
 

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" wrote in message
...
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

Content cell to determine which sheet to look at
 

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" wrote:

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

Content cell to determine which sheet to look at
 

Glad I could help, Mark.


"Mark" wrote in message
...
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" wrote:

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