Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sheet tab = to cell content | Excel Discussion (Misc queries) | |||
add up cell content to get answer at bottom of sheet | Excel Discussion (Misc queries) | |||
add up cell content to get answer at bottom of sheet | Excel Discussion (Misc queries) | |||
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? | Excel Worksheet Functions | |||
How to search and copy cell content to another sheet? | Excel Worksheet Functions |