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 !!!

=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 !!!

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 !!!

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