Hyperlink problem if space in Sheet Name

I've a problem using hyper link when I've a space between sheet name…… Please guide me in this regard.

=HYPERLINK(CELL("address",INDIRECT(A5&B5&"!C3")))
Where A5 is First Sheet Name "Jan" and B5 is second part of sheet name "Expense".

It works properly but when i've space in sheet name then unable to work. I've used below format but still no luck

=HYPERLINK(CELL("address",INDIRECT("'"&A4&" "&B4&"'"&"!C3"))

Also, If I have space in file name than its also not working.

Please give the solution in both cases of Space in File name & space in sheet name in my attached file.

Thanks in advance

Why to use formula?

Just type Click to go on spcific sheet. Then select cell. Go insert>hyperlink. Click Place in this document, select appropriate sheet and type the cell reference

I did it here, but I'm wondering if it suits u.

Thanks for your suggestion as I already know all these tricks but the prolbem is this i've almost 200 sheets and this is our management requirement to go on selected sheet by just selecting First Month & then Type ... It looks quite good to present some user friendly to your management.

Nope ...

I've corrected it but still the link having space in Sheet name is not working properly ...

e.g. Hyperlink on sheet JanIncome working but for Jan Expenses not working as there is space between Jan & Expenses .........

Try this,

=IF(ISERROR(HYPERLINK("#" & CELL("address",INDIRECT("'"&A4&SUBSTITUTE(B4,CHAR(160),CHAR(32))&"'"&"!C3")),"Click to Go on Specific Sheet")),"",HYPERLINK("#" & CELL("address",INDIRECT("'"&A4&SUBSTITUTE(B4,CHAR(160),CHAR(32))&"'"&"!C3")),"Click to Go on Specific Sheet"))

I added a non-breaking space CHAR(160) to the data validation, which means you can use the same formula for both cells.

Ha, I made a mistake. I modify your C4

Please Login or Register  to view this content.

Now, you can handle file and worksheet with space.

See attachment.

Hope this helps,
windknife

Thanks you very much WK & Andy ....... Its really help me to control these issues .... but telling you honestly i'm still trying to understand the use of Substitute ........ anyway again thanks

The substitute parts is there to allow you to remove the " " (space) part from your formula.
If you include the space in the formula then you need different formula for different sheet names.

The problem lies in data validation not outputing the leading space in the list of sheet names. In order to hold the space I used the non-breaking space character, CHAR(160). But this then causes the sheet name to be incorrect. To me and you Space and non-breaking space look the same. To excel they are different characters.

Personally I would have used a underscore instead of space. And been consistent with the naming convention. So all sheets would be named
MonthDescription

nospace or underscore to split names.

Originally Posted by Andy Pope

Personally I would have used a underscore instead of space. And been consistent with the naming convention. So all sheets would be named
MonthDescription

nospace or underscore to split names.

Just for the sake of info as i changed the File name and give - sign and + sign then still there were problem to hyperlink for simple formula so its is advisable not to use special character in File Name because it create problem in hyper linking or some other formula's .......

Again thank you very much for your valuable suggestion / advise .....