|
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 12:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com