Function With Lookup
 

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

Function With Lookup
 

Hi Dias,

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

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

--
Regards,

OssieMac


"Dias" wrote:

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

Function With Lookup
 

Hi again Dias,

this is a shorter version without one of the ampersands.

=INDIRECT(B1&"!A1")

--
Regards,

OssieMac


"OssieMac" wrote:

Hi Dias,

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

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

--
Regards,

OssieMac


"Dias" wrote:

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

Function With Lookup
 

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

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

Dias wrote:

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

--

Dave Peterson

Function With Lookup
 

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,FIN D("'",A1)+1,1)&"'!A1")

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Dias" wrote:

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

Function With Lookup
 

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:
Hi Dias,

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

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

Function With Lookup
 

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"))


--
Regards,

OssieMac


"Dias" wrote:

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:
Hi Dias,

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

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

Function With Lookup
 

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"))

--
Regards,

OssieMac


"OssieMac" wrote:

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"))


--
Regards,

OssieMac


"Dias" wrote:

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:
Hi Dias,

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

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

Function With Lookup
 

Thank all of you, this work perfect.

OssieMac escreveu:
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"))