How to take text from other cells to create a reference to a name

  • Thread starter Thread starter MB
  • Start date Start date
M

MB

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.
 
Try:
=indirect("basicsal"&a1)

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 !!!
Click to expand...
 
Back
Top