I have a list of worksheet names in a column from A1. These names can change and can be
text or alphanumeric and can contain spaces between text or between text and numbers. The
list is used to create worksheets with the same tab name. In column B starting at B1 I
have this formula

=HYPERLINK("#"&A1&"!A1",A1)

This is to allow me to jump to each of the relevant worksheets. The problem is that it
works fine when the tab names have no spaces but as soon as I have something like "Field
20" or "Field List" I get "reference not valid". I know I could concatenate everything
with a "_" or something in the spaces but for this exercise I really would like to avoid
that. My ideal situation is a variable sized tab name with or without spaces, ie no
restrictions other than things like ? / & etc, that I can click on to take me to the
worksheet, bearing in mind that the names can change so it would need to be indirect I
think. I would value any help.

Regards
Graham

Quite easy to rectify - amend your formula to this:

=HYPERLINK("#'"&A1&"'!A1",A1)

Note the apostrophe after the # and before the !, which allows you to
have spaces in your sheet names.

Hope this helps.

Pete

On Jul 2, 8:48*am, Graham H wrote:
I have a list of worksheet names in a column from A1. These names can change and can be
text or alphanumeric and can contain spaces between text or between text and numbers. The
list is used to create worksheets with the same tab name. In column B starting at B1 I
have this formula

=HYPERLINK("#"&A1&"!A1",A1)

This is to allow me to jump to each of the relevant worksheets. The problem is that it
works fine when the tab names have no spaces but as soon as I have something like "Field
20" or "Field List" I get "reference not valid". I know I could concatenate everything
with a "_" or something in the spaces but for this exercise I really would like to avoid
that. My ideal situation is a variable sized tab name with or without spaces, ie no
restrictions other than things like ? / & etc, that I can click on to take me to the
worksheet, bearing in mind that the names can change so it would need to be indirect I
think. I would value any help.

Regards
Graham

Pete_UK wrote:
Quite easy to rectify - amend your formula to this:

=HYPERLINK("#'"&A1&"'!A1",A1)

Note the apostrophe after the # and before the !, which allows you to
have spaces in your sheet names.

Hope this helps.

Pete
Many thanks for that Pete it fits the bill perfectly. Much appreciated.

Graham



On Jul 2, 8:48 am, Graham H wrote:
I have a list of worksheet names in a column from A1. These names can change and can be
text or alphanumeric and can contain spaces between text or between text and numbers. The
list is used to create worksheets with the same tab name. In column B starting at B1 I
have this formula

=HYPERLINK("#"&A1&"!A1",A1)

This is to allow me to jump to each of the relevant worksheets. The problem is that it
works fine when the tab names have no spaces but as soon as I have something like "Field
20" or "Field List" I get "reference not valid". I know I could concatenate everything
with a "_" or something in the spaces but for this exercise I really would like to avoid
that. My ideal situation is a variable sized tab name with or without spaces, ie no
restrictions other than things like ? / & etc, that I can click on to take me to the
worksheet, bearing in mind that the names can change so it would need to be indirect I
think. I would value any help.

Regards
Graham

You're welcome, Graham - thanks for feeding back.

Pete

On Jul 2, 10:00*am, Graham H wrote:

Many thanks for that Pete it fits the bill perfectly. Much appreciated.

Graham