Function With Lookup

[Dias]

Hi
I have a Workbook with a code that insert automatically a ws and fill
the name of that ws in a Master sheet in Column B with a link for that ws.

Wat I´m looking for is a function in row c to give me the value in A1 of
that ws.

In normal condition I can do CellC1=Sheet1!A1,is there a way to replace
"Sheet1" for the name of the sheet in column B

Thank you

Dias

 

[OssieMac]

Hi Dias,

If I understand your question correctoly then you should be able to use the
following.

=INDIRECT(B1&"!"&"A1")

 

[OssieMac]

Hi again Dias,

this is a shorter version without one of the ampersands.

=INDIRECT(B1&"!A1")

 

[Dave Peterson]

=indirect("'"&b1&"'!a1")

Sometimes those surrounding apostrophes are required--and they don't hurt if
they're not necessary.

 

[Shane Devenshire]

Hi,

You need the single apostrophy, for example, with sheet names that contain
any of the following:

Space (as in My Sheet) ,
-
!<
=
+
&
(
)
^
%
#
$
@
~
` (left quote symbol)
{
}
"
;
, (comma)
| (bar)


And finally the really trickiy one
' (single quote)
For this you must use two single quotes, so this one would read
=INDIRECT("'"&LEFT(A1,FIND("'",A1))&"'"&MID(A1,FIND("'",A1)+1,1)&"'!A1")

 

[Dias]

Hi
Thank you for the help.
To finish my project I only need a little thing.
Wen the cells in column B are empty I get #Ref Error,how do I take this
out.
Regards
Dias

OssieMac escreveu:

 

[OssieMac]

Hi Dias,

The other posts here are correct in saying that you should include the
single apostrophes around the sheet name as in the formula below in case you
have a space in the sheet name and as Dave says "they don't hurt if they're
not necessary."

Anyway the following will test for data in B1 and if blank then the formula
returns a blank.

=IF(B1 = "","",INDIRECT("'"&B1&"'!A1"))

 

[OssieMac]

Hi again Dias,

Discard my previous formula and use this one. In the previous formula if the
sheet name is incorrect then it will still display an error. This one
accounts for blank or incorrect sheet name.

=IF(ISERROR(INDIRECT("'"&B1&"'!A1")),"",INDIRECT("'"&B1&"'!A1"))

 

[Dias]

Thank all of you, this work perfect.

OssieMac escreveu: