Is there a worksheet function that returns the name of the sheet the cell is
on? (Much like the way ROW() and COLUMN() return the cell's position)

=ROW()
=COLUMN()
=SHEETNAME()

the ADDRESS function I thought would work but it only accepts a literal
string that is then appended to the address generated by ROW and COLUMN.

=ADDRESS(ROW(),COLUMN(),,,"Sheet1")

I need this:

=ADDRESS(ROW(),COLUMN(),,,???YouTellMe)

TIA
Charlie

See this page Charlie
http://www.xldynamic.com/source/xld.xlFAQ0002.html

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Charlie" wrote in message ...
Is there a worksheet function that returns the name of the sheet the cell is
on? (Much like the way ROW() and COLUMN() return the cell's position)

=ROW()
=COLUMN()
=SHEETNAME()

the ADDRESS function I thought would work but it only accepts a literal
string that is then appended to the address generated by ROW and COLUMN.

=ADDRESS(ROW(),COLUMN(),,,"Sheet1")

I need this:

=ADDRESS(ROW(),COLUMN(),,,???YouTellMe)

TIA
Charlie

Very good idea. Now you see why I was wanting a SHEETNAME function! What a
bunch of rigamarole to get something so simple! I too found the CELL
function but noticed the changing reference problem. I didn't think about
the anchor-it-with-a-cell-reference idea. Looks good, I may give it a try.

I'm having a problem with workbooks not recalculating as expected when
opened with a newer version of Excel. Reports are generated on a server and
when viewed there they work fine. But when they are copied to a local
workstation having a newer version of Excel they don't work.

I have verified that the workbook is being recalculated when opened for the
first time with the newer Excel. This is not a problem in itself. The
problem is that the sheets are not activated as the recalculating is done.
Some of my formulas use macro functions that are expecting the cells to be on
the active sheet. This results in all sheets having the values from whatever
sheet was active when the workbook was closed.

So... to make a long story longer... I was going to modify my functions to
use the actual sheets(sheetname).cells of the formula location. Problem is
my functions are also accepting named ranges (sheet-level), which I presume
will also fail due to the sheet not being active. Oh, it never ends!

I think I'll just remove the macro functions from the formulas and run the
functions in the report macros at gen time.

Thanks for the help.

Charlie

"Ron de Bruin" wrote:

See this page Charlie
http://www.xldynamic.com/source/xld.xlFAQ0002.html

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Charlie" wrote in message ...
Is there a worksheet function that returns the name of the sheet the cell is
on? (Much like the way ROW() and COLUMN() return the cell's position)

=ROW()
=COLUMN()
=SHEETNAME()

the ADDRESS function I thought would work but it only accepts a literal
string that is then appended to the address generated by ROW and COLUMN.

=ADDRESS(ROW(),COLUMN(),,,"Sheet1")

I need this:

=ADDRESS(ROW(),COLUMN(),,,???YouTellMe)

TIA
Charlie