Variable Worksheet Names

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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
 
Thank you, OssieMac!


OssieMac said:
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
Click to expand...
 
Back
Top