Help needed on Indirect Function

[vranjit138]

Hi,
I have come across a strange problem while using indirect function.

In my workbook i have two sheets 1. projectdata 2. Report
in projectdata sheet i have data from Cell A1 to B20
In Report sheet in Cell A1 i have put the following text - projectdata!A1:B20
now using the main menu>> formula >> name manager >>create a new named range >> i give it a name pivotrange and in the refers to box i have put = indirect($A$1)
iam able to create a pivot table by using the named range - pivotrange
but when i change the name of the sheet from projectdata to project data, the pivot table shows "Reference isn't valid"
my question is can we not create a named range using indirect function where the sheet name has more than 2 words?
Iam using Office 365.
please help me to resolve this.

Thanks in ad

 

[etaf]

not tried it
but you need ' if there is a space

'project data'!A1:B20

did you add the '

i tend with indirect to add the ' any way , with a cell so if it does have a space its captured

so something like
indirect("'"&Cell&"'!B2:D20")

so now the cell can have
Projectdata
OR
project data

 

[vranjit138]

Thanks a ton Etaf .. it worked perfectly

 

[etaf]

you are welcome