Indirect problem

[superkopite]

Hiya guys and gals

I have the following code;

=INDIRECT("E2"!, E7)

From what i understand from the help files this should work, unless i
am being stupid (highly probable)

What I want it to do is for the host cell to display the value of cell
E7 from another worksheet, the name of the work sheet is in cell E2.

Like i said i have searched the help files and this seems to be the
method that is best.

Regards
James

 

[Bob Phillips]

=INDIRECT("'"&E2&"'!"&E7)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"superkopite" <[email protected]>
wrote in message
news:[email protected]...

 

[Niek Otten]

=INDIRECT("Sheet2!"&E7)

Is your sheet called E2?

You probably mean

=INDIRECT(E2&"!"&E7)

??


--
Kind regards,

Niek Otten

"superkopite" <[email protected]>
wrote in message
news:[email protected]...

 

[superkopite]

This seems to throw back an #ref error

Regards

James

 

[Gary''s Student]

Let's say cell E2 contains the text Sheet3
then
=INDIRECT(E2 & "!" & "E7") is what you want.


The trick is that between the parenthesis you want a string that represents
an address

 

[superkopite]

hi Nek

no that doesn't seem to work either

The name of the worksheet is located in cell E7

Regards

James

 

[Sloth]

=INDIRECT(E2&"!E7")

If E2 contains a string with spaces (ie: "Sheet 1" instead of "Sheet1") you
will need to use this formula

=INDIRECT("'"&E2&"'"&"!E7")

You want a text string that looks like this...
Sheet1!E7
or
'Sheet 1'!E7

 

[superkopite]

For some reason this is still returning a #ref error.

It is also highlighting the E2 part of the formula to indicate that
this the main part of the problem

Regards

James

 

[Dave Peterson]

I bet the name of the worksheet is misspelled in E2.

 

[Bob Phillips]

Maybe this then

=INDIRECT("'"&E2&"'!E7")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"superkopite" <[email protected]>
wrote in message
news:[email protected]...

 

[superkopite]

Thanks a lot guys

Sloth your forumla sorted it, I think it was because the worksheet name
contain spaces, as dave suggested.

Once again thank everyone

Kind Regards

James

 

[Sloth]

It's probably because whatever is in cell E7 doesn't look like a cell
reference. I think E7 just need to be in quotes as well, if I understand the
problem right.

I bet the name of the worksheet is misspelled in E2.