Variable Worksheet Names

[Guest]

Hello,

I have been trying to get this to work.

=CELL("filename",Sheet1!$A$1)

I have an existing worksheet with multiple tabs. I want to create a named
range of worksheet names for a sequential portion of these tabs.

If I try it exacly as it says, I get a value not available error, probably
because I do not have a sheet named Sheet1.

If I try to replace Sheet1 with the name of my first worksheet for this
named range, Excel asks me where I want to save it.

=CELL("filename",Day One!$A$1)

If I add double single quotes, I get a formula error message.

=CELL("filename",''Day One''!$A$1)

What am I missing?

Also, once this is working, I would like it to pickup any newly added
worksheets within this range of worksheets and add the name to my named range
of worksheet names. Is this possible?

Thanks for your help.

 

[Guest]

Hi Karen,

I am a bit confused as to what you are trying to do here. Are you working on
a worksheet or in VBA for Excel?

If on a worksheet then to make something equal another range then it should
be something like this:-

=[Book2]Sheet1!$A$1 (Where Book2 is another workbook.)

It will return an error if the workbook or sheet reference does not exist.

The CELL function returns information about a cell. Look it up in Help.

Regards,

OssieMac

 

[Guest]

I am trying to follow these instructions, unsuccessfully. I am in Excel, not
VB.

http://www.ozgrid.com/Excel/variable-worksheet-names.htm

Thanks

 

[Guest]

Hi Karen,

Now I understand a little better. The use of the CELL function is correct
for the circumstances and it is returning information as I said it should. In
this case it is returning the filename.

Now your specific problem is the worksheet name with the space. Because of
the space you need to enclose it in single quotes not double quotes.

=CELL("filename",'Day One'!$A$1)

Regards,

OssieMac

 

[Guest]

Thank you, OssieMac!

Hi Karen,

Now I understand a little better. The use of the CELL function is correct
for the circumstances and it is returning information as I said it should. In
this case it is returning the filename.

Now your specific problem is the worksheet name with the space. Because of
the space you need to enclose it in single quotes not double quotes.

=CELL("filename",'Day One'!$A$1)

Regards,

OssieMac