How to take text from other cells to create a reference to a name
I have a number of simlar named ranges - e.g. basicsalprnaip, basicsalprtodc,
basicsalprcoco etc etc ...... I wish to create a text string within each sheet of my workbook that creates a ref to the named range by taking the last 5 characters from the current worksheet - e.g. prnaip in the first example above. e.g. =basicsal&A1 (A1 cell containes prnaip) However this does not create a formula referring to the named range. Hope this explanation is clear!! Please help !!! |
How to take text from other cells to create a reference to a name
=INDIRECT("basicsal"&A1)
On 3 Lis, 14:18, MB wrote: I have a number of simlar named ranges - e.g. basicsalprnaip, basicsalprtodc, basicsalprcoco *etc etc ...... I wish to create a text string within each sheet of my workbook that creates a ref to the named range by taking the last 5 characters from the current worksheet - e.g. prnaip in the first example above. e.g. =basicsal&A1 (A1 cell containes prnaip) However this does not create a formula referring to the named range. *Hope this explanation is clear!! *Please help !!! |
How to take text from other cells to create a reference to a name
I'm not sure I've got this right, but perhaps it'll give you enough
information to do what you want. Note that CELL() will not return a value until the workbook has been saved to disk. the CELL() function will give you the name of the workbook and worksheet that a cell references is on. For example, if you put this =CELL("filename",A1) on Sheet1 somewhere it will give you something like C:\Users\UserName\Documents\[WorkbookName.xls]Sheet1 so =Right(Cell("filename",A1),5) will give you the right 5 characters of that string, presumably the last 5 characters of the sheet name. INDIRECT() uses the parameter as the address to return information from, so =INDIRECT(RIGHT(CELL("filename",A1),5)) would return the value from a range named "heet1" in my example. Take it one more step and =INDIRECT("basicsal" & RIGHT(CELL("filename",A1),5)) would return the value from a range named 'basicsalheet1'. Hope this helps. "MB" wrote: I have a number of simlar named ranges - e.g. basicsalprnaip, basicsalprtodc, basicsalprcoco etc etc ...... I wish to create a text string within each sheet of my workbook that creates a ref to the named range by taking the last 5 characters from the current worksheet - e.g. prnaip in the first example above. e.g. =basicsal&A1 (A1 cell containes prnaip) However this does not create a formula referring to the named range. Hope this explanation is clear!! Please help !!! |
How to take text from other cells to create a reference to a name
Try:
=indirect("basicsal"&a1) MB wrote: I have a number of simlar named ranges - e.g. basicsalprnaip, basicsalprtodc, basicsalprcoco etc etc ...... I wish to create a text string within each sheet of my workbook that creates a ref to the named range by taking the last 5 characters from the current worksheet - e.g. prnaip in the first example above. e.g. =basicsal&A1 (A1 cell containes prnaip) However this does not create a formula referring to the named range. Hope this explanation is clear!! Please help !!! -- Dave Peterson |
All times are GMT +1. The time now is 02:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com