Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 989
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 860
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 989
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 860
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
sheet tab = to cell content reaa Excel Discussion (Misc queries) 5 February 26th 07 08:06 PM
add up cell content to get answer at bottom of sheet Robert Excel Discussion (Misc queries) 1 July 11th 06 11:31 PM
add up cell content to get answer at bottom of sheet Robert Excel Discussion (Misc queries) 2 July 11th 06 10:18 PM
in vba what command is used to determine if a particular cell on a particular sheet changed? some kind of event? how to get the old and new value of the cell? Daniel Excel Worksheet Functions 1 June 23rd 05 07:53 PM
How to search and copy cell content to another sheet? Roel Excel Worksheet Functions 1 March 7th 05 02:56 PM


All times are GMT +1. The time now is 04:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"