| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Worksheet Names
I have a workbook with multiple worksheets. If i have a master sheet with a list of sheet references is there a way i can reference a cell via the sheet reference? EG. If each sheet refers to a case, then the master has a list of 10 cases in column A (which will change). Sheet Ref Case Value 1 A 10 B 15 F 5 C 2 D 3 I (Please note the sheet ref is the number and the letter is supposed to be a case name but the formating is moving it too close together...) The value i am looking for will be in the same cell in each sheet (lets say C5) so i just need to to change the sheet reference. I have tried using CONCATENATE preceeded by the INDIRECT formula but this is returning #REF! Any ideas? -- chalky ------------------------------------------------------------------------ chalky's Profile: http://www.excelforum.com/member.php...o&userid=23758 View this thread: http://www.excelforum.com/showthread...hreadid=483089 |
|
#2
|
|||
|
|||
|
Worksheet Names
If your sheet name is say 1A|, then use
=INDIRECT("'"&A1&B1&"'!C5") -- HTH RP (remove nothere from the email address if mailing direct) "chalky" wrote in message ... I have a workbook with multiple worksheets. If i have a master sheet with a list of sheet references is there a way i can reference a cell via the sheet reference? EG. If each sheet refers to a case, then the master has a list of 10 cases in column A (which will change). Sheet Ref Case Value 1 A 10 B 15 F 5 C 2 D 3 I (Please note the sheet ref is the number and the letter is supposed to be a case name but the formating is moving it too close together...) The value i am looking for will be in the same cell in each sheet (lets say C5) so i just need to to change the sheet reference. I have tried using CONCATENATE preceeded by the INDIRECT formula but this is returning #REF! Any ideas? -- chalky ------------------------------------------------------------------------ chalky's Profile: http://www.excelforum.com/member.php...o&userid=23758 View this thread: http://www.excelforum.com/showthread...hreadid=483089 |
|
#3
|
|||
|
|||
|
Worksheet Names
Ah perfect, that is exactly what i was trying to get to with CONCATENATE but your way is loads better. Well for a start it works! Cheers Chris -- chalky ------------------------------------------------------------------------ chalky's Profile: http://www.excelforum.com/member.php...o&userid=23758 View this thread: http://www.excelforum.com/showthread...hreadid=483089 |
|
#4
|
|||
|
|||
|
Worksheet Names
Chris,
CONCATENATE is a completely unnecessary function, & does it just as well, and is more self-explanatory IMO. -- HTH RP (remove nothere from the email address if mailing direct) "chalky" wrote in message ... Ah perfect, that is exactly what i was trying to get to with CONCATENATE but your way is loads better. Well for a start it works! Cheers Chris -- chalky ------------------------------------------------------------------------ chalky's Profile: http://www.excelforum.com/member.php...o&userid=23758 View this thread: http://www.excelforum.com/showthread...hreadid=483089 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automating Worksheet Names | Excel Worksheet Functions | |||
| Lookups using worksheet names | Excel Discussion (Misc queries) | |||
| Dynamic Worksheet Names | Links and Linking in Excel | |||
| Search/Match between 2 x separate Worksheets and populate result in third worksheet | Excel Discussion (Misc queries) | |||
| Indirect reference from one worksheet to another | Excel Worksheet Functions | |||
Linear Mode
