& reference answering as Date Serial # not actual text

[Kevin M]

Hi all, TIA..
I'm trying to reference a user defined date in B4, it's
formatted "MMM dd, yyyy" in C8 i'm trying to use:

="["&$B$4&"]"&$C$8 to have it pull from another sheet
named by date, ie: Nov 25, 2004.xls, hence why i
formatted B4 as MMM dd, yyyy. The issue is that it
returns the date serial number instead of the entered
text, so the answer is [38316] instead of the text. The
user would like to enter a date to look up and have Excel
return the appropriate days' responses. If there's an
easier way to pull this, i'm all ears!

Thanks again,
Kevin M

 

[Frank Kabel]

Hi
try
=INDIRECT("'[" & TEXT(B4,"MMM dd,yyy") & ".xls]sheet1'!C8")

Note: This only works if the other file is opened!!

 

[Kevin M]

As always, thanks Frank, do you know of a method for
pulling if it's closed? Sounds like what the user is
after is having her subordinates be able to enter a date
in one sheet with nothing else open.

Kevin M

-----Original Message-----
Hi
try
=INDIRECT("'[" & TEXT(B4,"MMM dd,yyy") & ".xls]sheet1'! C8")

Note: This only works if the other file is opened!!
--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag news:187801c4d706$1382a080 [email protected]...

Hi all, TIA..
I'm trying to reference a user defined date in B4, it's
formatted "MMM dd, yyyy" in C8 i'm trying to use:

="["&$B$4&"]"&$C$8 to have it pull from another sheet
named by date, ie: Nov 25, 2004.xls, hence why i
formatted B4 as MMM dd, yyyy. The issue is that it
returns the date serial number instead of the entered
text, so the answer is [38316] instead of the text. The
user would like to enter a date to look up and have Excel
return the appropriate days' responses. If there's an
easier way to pull this, i'm all ears!

Thanks again,
Kevin M

.

 

[Frank Kabel]

Hi
download morefunc.xll (http://longre.free.fr/english) and use the
function
INDIRECT.EXT

Note: Then you also must include the file path

--
Regards
Frank Kabel
Frankfurt, Germany

As always, thanks Frank, do you know of a method for
pulling if it's closed? Sounds like what the user is
after is having her subordinates be able to enter a date
in one sheet with nothing else open.

Kevin M

-----Original Message-----
Hi
try
=INDIRECT("'[" & TEXT(B4,"MMM dd,yyy") & ".xls]sheet1'! C8")

Note: This only works if the other file is opened!!
--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag news:187801c4d706$1382a080 [email protected]...

Hi all, TIA..
I'm trying to reference a user defined date in B4, it's
formatted "MMM dd, yyyy" in C8 i'm trying to use:

="["&$B$4&"]"&$C$8 to have it pull from another sheet
named by date, ie: Nov 25, 2004.xls, hence why i
formatted B4 as MMM dd, yyyy. The issue is that it
returns the date serial number instead of the entered
text, so the answer is [38316] instead of the text. The
user would like to enter a date to look up and have Excel
return the appropriate days' responses. If there's an
easier way to pull this, i'm all ears!

Thanks again,
Kevin M

.

 

[Kevin M]

Okay Frank.. i've added the add-in, using INDIRECT.EXT
now, but i'm sorry, i can't seem to finish the formula to
save my life right now, it's REF#'ing. i've entered it
exactly as such and can't see where the REF comes from:

=INDIRECT.EXT("C:\Documents and
Settings\kmcclement\Desktop'["&TEXT($B$4,"MMM dd,yyy")
&".xls]'data!C8")

Thanks again sir..
Kevin M

-----Original Message-----
Hi
download morefunc.xll (http://longre.free.fr/english) and use the
function
INDIRECT.EXT

Note: Then you also must include the file path

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag news:ace001c4d708$46d2b220 [email protected]...

As always, thanks Frank, do you know of a method for
pulling if it's closed? Sounds like what the user is
after is having her subordinates be able to enter a date
in one sheet with nothing else open.

Kevin M

-----Original Message-----
Hi
try
=INDIRECT("'[" & TEXT(B4,"MMM dd,yyy") & ".xls]

sheet1'!
C8")

Note: This only works if the other file is opened!!
--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag [email protected]...
Hi all, TIA..
I'm trying to reference a user defined date in B4, it's
formatted "MMM dd, yyyy" in C8 i'm trying to use:

="["&$B$4&"]"&$C$8 to have it pull from another sheet
named by date, ie: Nov 25, 2004.xls, hence why i
formatted B4 as MMM dd, yyyy. The issue is that it
returns the date serial number instead of the entered
text, so the answer is [38316] instead of the text. The
user would like to enter a date to look up and have Excel
return the appropriate days' responses. If there's an
easier way to pull this, i'm all ears!

Thanks again,
Kevin M

.

.

 

[Frank Kabel]

Hi
try:
=INDIRECT.EXT("'C:\Documents and
Settings\kmcclement\Desktop["&TEXT($B$4,"MMM dd,yyy")
&".xls]'data!C8")

Also it is wlways easier to first create a direct cell reference, copy
the cell reference from the formula bar and insert this in
INDIRECT.EXT. Then replace the parts with other formulas.

In you case at least a missing apostrophe at the beginning and a
apostrophe before the square bracket caused the #REF error

--
Regards
Frank Kabel
Frankfurt, Germany

Okay Frank.. i've added the add-in, using INDIRECT.EXT
now, but i'm sorry, i can't seem to finish the formula to
save my life right now, it's REF#'ing. i've entered it
exactly as such and can't see where the REF comes from:

=INDIRECT.EXT("C:\Documents and
Settings\kmcclement\Desktop'["&TEXT($B$4,"MMM dd,yyy")
&".xls]'data!C8")

Thanks again sir..
Kevin M

-----Original Message-----
Hi
download morefunc.xll (http://longre.free.fr/english) and use the
function
INDIRECT.EXT

Note: Then you also must include the file path

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag news:ace001c4d708$46d2b220 [email protected]...

As always, thanks Frank, do you know of a method for
pulling if it's closed? Sounds like what the user is
after is having her subordinates be able to enter a date
in one sheet with nothing else open.

Kevin M

-----Original Message-----
Hi
try
=INDIRECT("'[" & TEXT(B4,"MMM dd,yyy") & ".xls] sheet1'!
C8")

Note: This only works if the other file is opened!!
--
Regards
Frank Kabel
Frankfurt, Germany

"Kevin M" <[email protected]>
schrieb im
Newsbeitrag [email protected]...
Hi all, TIA..
I'm trying to reference a user defined date in B4, it's
formatted "MMM dd, yyyy" in C8 i'm trying to use:

="["&$B$4&"]"&$C$8 to have it pull from another sheet
named by date, ie: Nov 25, 2004.xls, hence why i
formatted B4 as MMM dd, yyyy. The issue is that it
returns the date serial number instead of the entered
text, so the answer is [38316] instead of the text. The
user would like to enter a date to look up and have
Excel
return the appropriate days' responses. If there's an
easier way to pull this, i'm all ears!

Thanks again,
Kevin M

.

.

 

[Kevin M]

Put a fork in me, i'm done. It's still #REF'ing. It's
letter for letter the same as your fix. I've stepped
through (formula audit) and it looks 100% fine. I'm
trying to tweak, to no avail. i used:
=cell("filename",data!c8) just to make sure that the file
location was correct as well.

Thank you very much for the assistance Frank, much
appreciated.

Kevin M

-----Original Message-----
Hi
try:
=INDIRECT.EXT("'C:\Documents and
Settings\kmcclement\Desktop["&TEXT($B$4,"MMM dd,yyy")
&".xls]'data!C8")

Also it is wlways easier to first create a direct cell reference, copy
the cell reference from the formula bar and insert this in
INDIRECT.EXT. Then replace the parts with other formulas.

In you case at least a missing apostrophe at the beginning and a
apostrophe before the square bracket caused the #REF error

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag news:ad1101c4d70c$df252770 [email protected]...

Okay Frank.. i've added the add-in, using INDIRECT.EXT
now, but i'm sorry, i can't seem to finish the formula to
save my life right now, it's REF#'ing. i've entered it
exactly as such and can't see where the REF comes from:

=INDIRECT.EXT("C:\Documents and
Settings\kmcclement\Desktop'["&TEXT($B$4,"MMM dd,yyy")
&".xls]'data!C8")

Thanks again sir..
Kevin M

-----Original Message-----
Hi
download morefunc.xll (http://longre.free.fr/english) and use the
function
INDIRECT.EXT

Note: Then you also must include the file path

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag [email protected]...
As always, thanks Frank, do you know of a method for
pulling if it's closed? Sounds like what the user is
after is having her subordinates be able to enter a date
in one sheet with nothing else open.

Kevin M

-----Original Message-----
Hi
try
=INDIRECT("'[" & TEXT(B4,"MMM dd,yyy") & ".xls] sheet1'!
C8")

Note: This only works if the other file is opened!!
--
Regards
Frank Kabel
Frankfurt, Germany

"Kevin M" <[email protected]>
schrieb im
Newsbeitrag [email protected]...
Hi all, TIA..
I'm trying to reference a user defined date in

B4,
it's

formatted "MMM dd, yyyy" in C8 i'm trying to use:

="["&$B$4&"]"&$C$8 to have it pull from another sheet
named by date, ie: Nov 25, 2004.xls, hence why i
formatted B4 as MMM dd, yyyy. The issue is that it
returns the date serial number instead of the entered
text, so the answer is [38316] instead of the

text.
The

user would like to enter a date to look up and have
Excel
return the appropriate days' responses. If

there's
an

easier way to pull this, i'm all ears!

Thanks again,
Kevin M

.


.

.

 

[Frank Kabel]

Hi
could you post the link you want to create and the link the string
concatenation formula returns

--
Regards
Frank Kabel
Frankfurt, Germany

Put a fork in me, i'm done. It's still #REF'ing. It's
letter for letter the same as your fix. I've stepped
through (formula audit) and it looks 100% fine. I'm
trying to tweak, to no avail. i used:
=cell("filename",data!c8) just to make sure that the file
location was correct as well.

Thank you very much for the assistance Frank, much
appreciated.

Kevin M

-----Original Message-----
Hi
try:
=INDIRECT.EXT("'C:\Documents and
Settings\kmcclement\Desktop["&TEXT($B$4,"MMM dd,yyy")
&".xls]'data!C8")

Also it is wlways easier to first create a direct cell reference, copy
the cell reference from the formula bar and insert this in
INDIRECT.EXT. Then replace the parts with other formulas.

In you case at least a missing apostrophe at the beginning and a
apostrophe before the square bracket caused the #REF error

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag news:ad1101c4d70c$df252770 [email protected]...

Okay Frank.. i've added the add-in, using INDIRECT.EXT
now, but i'm sorry, i can't seem to finish the formula to
save my life right now, it's REF#'ing. i've entered it
exactly as such and can't see where the REF comes from:

=INDIRECT.EXT("C:\Documents and
Settings\kmcclement\Desktop'["&TEXT($B$4,"MMM dd,yyy")
&".xls]'data!C8")

Thanks again sir..
Kevin M

-----Original Message-----
Hi
download morefunc.xll (http://longre.free.fr/english)
and use the
function
INDIRECT.EXT

Note: Then you also must include the file path

--
Regards
Frank Kabel
Frankfurt, Germany

"Kevin M" <[email protected]>
schrieb im
Newsbeitrag [email protected]...
As always, thanks Frank, do you know of a method for
pulling if it's closed? Sounds like what the user is
after is having her subordinates be able to enter a
date
in one sheet with nothing else open.

Kevin M

-----Original Message-----
Hi
try
=INDIRECT("'[" & TEXT(B4,"MMM dd,yyy") & ".xls]
sheet1'!
C8")

Note: This only works if the other file is opened!!
--
Regards
Frank Kabel
Frankfurt, Germany

"Kevin M" <[email protected]>
schrieb im
Newsbeitrag [email protected]...
Hi all, TIA..
I'm trying to reference a user defined date in B4,
it's
formatted "MMM dd, yyyy" in C8 i'm trying to use:

="["&$B$4&"]"&$C$8 to have it pull from another
sheet
named by date, ie: Nov 25, 2004.xls, hence why i
formatted B4 as MMM dd, yyyy. The issue is that it
returns the date serial number instead of the
entered
text, so the answer is [38316] instead of the text.
The
user would like to enter a date to look up and have
Excel
return the appropriate days' responses. If there's
an
easier way to pull this, i'm all ears!

Thanks again,
Kevin M

.


.

.

 

[Kevin M]

User enters date in B4 of [Lookup Sheet]
C8 in [Lookup Sheet] should return the contents of C8
from "Nov 25, 2004.xls"

I used this naming convention for simplicity as it
matches the dates entered by the user. Each dated book
uses the sheet name "Data"

In [Lookup Sheet]Template!C8 i have the INDIRECT.EXT:

=INDIRECT.EXT("'C:\Documents and
Settings\kmcclement\Desktop["&TEXT($B$4,"MMM dd, yyyy")
&".xls]'data!c8")

This returns #REF!

So, [Lookup Sheet]template'C8 should equal [Nov 25, 2004]
Data!C8

Kevin

-----Original Message-----
Hi
could you post the link you want to create and the link the string
concatenation formula returns

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag news:a42501c4d712$b2230840 [email protected]...

Put a fork in me, i'm done. It's still #REF'ing. It's
letter for letter the same as your fix. I've stepped
through (formula audit) and it looks 100% fine. I'm
trying to tweak, to no avail. i used:
=cell("filename",data!c8) just to make sure that the file
location was correct as well.

Thank you very much for the assistance Frank, much
appreciated.

Kevin M

-----Original Message-----
Hi
try:
=INDIRECT.EXT("'C:\Documents and
Settings\kmcclement\Desktop["&TEXT($B$4,"MMM dd,yyy")
&".xls]'data!C8")

Also it is wlways easier to first create a direct cell reference, copy
the cell reference from the formula bar and insert

this
in

INDIRECT.EXT. Then replace the parts with other formulas.

In you case at least a missing apostrophe at the beginning and a
apostrophe before the square bracket caused the #REF error

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag [email protected]...
Okay Frank.. i've added the add-in, using INDIRECT.EXT
now, but i'm sorry, i can't seem to finish the

formula
to

save my life right now, it's REF#'ing. i've entered it
exactly as such and can't see where the REF comes from:

=INDIRECT.EXT("C:\Documents and
Settings\kmcclement\Desktop'["&TEXT($B$4,"MMM dd,yyy")
&".xls]'data!C8")

Thanks again sir..
Kevin M

-----Original Message-----
Hi
download morefunc.xll (http://longre.free.fr/english)
and use the
function
INDIRECT.EXT

Note: Then you also must include the file path

--
Regards
Frank Kabel
Frankfurt, Germany

"Kevin M" <[email protected]>
schrieb im
Newsbeitrag [email protected]...
As always, thanks Frank, do you know of a method for
pulling if it's closed? Sounds like what the user is
after is having her subordinates be able to enter a
date
in one sheet with nothing else open.

Kevin M

-----Original Message-----
Hi
try
=INDIRECT("'[" & TEXT(B4,"MMM dd,yyy") & ".xls]
sheet1'!
C8")

Note: This only works if the other file is opened!!
--
Regards
Frank Kabel
Frankfurt, Germany

"Kevin M"

schrieb im
Newsbeitrag [email protected]...
Hi all, TIA..
I'm trying to reference a user defined date in B4,
it's
formatted "MMM dd, yyyy" in C8 i'm trying to use:

="["&$B$4&"]"&$C$8 to have it pull from another
sheet
named by date, ie: Nov 25, 2004.xls, hence why i
formatted B4 as MMM dd, yyyy. The issue is

that
it

returns the date serial number instead of the
entered
text, so the answer is [38316] instead of the text.
The
user would like to enter a date to look up and have
Excel
return the appropriate days' responses. If there's
an
easier way to pull this, i'm all ears!

Thanks again,
Kevin M

.


.


.

.

 

[Frank Kabel]

Hi
maybe a missing space before yyyy

--
Regards
Frank Kabel
Frankfurt, Germany

User enters date in B4 of [Lookup Sheet]
C8 in [Lookup Sheet] should return the contents of C8
from "Nov 25, 2004.xls"

I used this naming convention for simplicity as it
matches the dates entered by the user. Each dated book
uses the sheet name "Data"

In [Lookup Sheet]Template!C8 i have the INDIRECT.EXT:

=INDIRECT.EXT("'C:\Documents and
Settings\kmcclement\Desktop["&TEXT($B$4,"MMM dd, yyyy")
&".xls]'data!c8")

This returns #REF!

So, [Lookup Sheet]template'C8 should equal [Nov 25, 2004]
Data!C8

Kevin

-----Original Message-----
Hi
could you post the link you want to create and the link the string
concatenation formula returns

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag news:a42501c4d712$b2230840 [email protected]...

Put a fork in me, i'm done. It's still #REF'ing. It's
letter for letter the same as your fix. I've stepped
through (formula audit) and it looks 100% fine. I'm
trying to tweak, to no avail. i used:
=cell("filename",data!c8) just to make sure that the file
location was correct as well.

Thank you very much for the assistance Frank, much
appreciated.

Kevin M

-----Original Message-----
Hi
try:
=INDIRECT.EXT("'C:\Documents and
Settings\kmcclement\Desktop["&TEXT($B$4,"MMM dd,yyy")
&".xls]'data!C8")

Also it is wlways easier to first create a direct cell
reference, copy
the cell reference from the formula bar and insert this
in
INDIRECT.EXT. Then replace the parts with other formulas.

In you case at least a missing apostrophe at the
beginning and a
apostrophe before the square bracket caused the #REF
error

--
Regards
Frank Kabel
Frankfurt, Germany

"Kevin M" <[email protected]>
schrieb im
Newsbeitrag [email protected]...
Okay Frank.. i've added the add-in, using INDIRECT.EXT
now, but i'm sorry, i can't seem to finish the formula
to
save my life right now, it's REF#'ing. i've entered it
exactly as such and can't see where the REF comes from:

=INDIRECT.EXT("C:\Documents and
Settings\kmcclement\Desktop'["&TEXT($B$4,"MMM dd,yyy")
&".xls]'data!C8")

Thanks again sir..
Kevin M

-----Original Message-----
Hi
download morefunc.xll (http://longre.free.fr/english)
and use the
function
INDIRECT.EXT

Note: Then you also must include the file path

--
Regards
Frank Kabel
Frankfurt, Germany

"Kevin M" <[email protected]>
schrieb im
Newsbeitrag [email protected]...
As always, thanks Frank, do you know of a method for
pulling if it's closed? Sounds like what the user is
after is having her subordinates be able to enter a
date
in one sheet with nothing else open.

Kevin M

-----Original Message-----
Hi
try
=INDIRECT("'[" & TEXT(B4,"MMM dd,yyy") & ".xls]
sheet1'!
C8")

Note: This only works if the other file is opened!!
--
Regards
Frank Kabel
Frankfurt, Germany

"Kevin M"

schrieb im
Newsbeitrag [email protected]...
Hi all, TIA..
I'm trying to reference a user defined date in
B4,
it's
formatted "MMM dd, yyyy" in C8 i'm trying to use:

="["&$B$4&"]"&$C$8 to have it pull from another
sheet
named by date, ie: Nov 25, 2004.xls, hence why i
formatted B4 as MMM dd, yyyy. The issue is that
it
returns the date serial number instead of the
entered
text, so the answer is [38316] instead of the
text.
The
user would like to enter a date to look up and
have
Excel
return the appropriate days' responses. If
there's
an
easier way to pull this, i'm all ears!

Thanks again,
Kevin M

.


.


.

.

 

[Kevin M]

Yeah, tried that.. also made it yyyy instead of yyy. I've
sent the sheets hopoing that if you see it, it'll come
into perspective.

Thanks again Frank,
Kevin M

-----Original Message-----
Hi
maybe a missing space before yyyy

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag news:191501c4d715$12e59f60 [email protected]...

User enters date in B4 of [Lookup Sheet]
C8 in [Lookup Sheet] should return the contents of C8
from "Nov 25, 2004.xls"

I used this naming convention for simplicity as it
matches the dates entered by the user. Each dated book
uses the sheet name "Data"

In [Lookup Sheet]Template!C8 i have the INDIRECT.EXT:

=INDIRECT.EXT("'C:\Documents and
Settings\kmcclement\Desktop["&TEXT($B$4,"MMM dd, yyyy")
&".xls]'data!c8")

This returns #REF!

So, [Lookup Sheet]template'C8 should equal [Nov 25, 2004]
Data!C8

Kevin

-----Original Message-----
Hi
could you post the link you want to create and the

link
the string

concatenation formula returns

--
Regards
Frank Kabel
Frankfurt, Germany

Newsbeitrag [email protected]...
Put a fork in me, i'm done. It's still #REF'ing. It's
letter for letter the same as your fix. I've stepped
through (formula audit) and it looks 100% fine. I'm
trying to tweak, to no avail. i used:
=cell("filename",data!c8) just to make sure that the file
location was correct as well.

Thank you very much for the assistance Frank, much
appreciated.

Kevin M

-----Original Message-----
Hi
try:
=INDIRECT.EXT("'C:\Documents and
Settings\kmcclement\Desktop["&TEXT($B$4,"MMM dd,yyy")
&".xls]'data!C8")

Also it is wlways easier to first create a direct cell
reference, copy
the cell reference from the formula bar and insert this
in
INDIRECT.EXT. Then replace the parts with other formulas.

In you case at least a missing apostrophe at the
beginning and a
apostrophe before the square bracket caused the #REF
error

--
Regards
Frank Kabel
Frankfurt, Germany

"Kevin M" <[email protected]>
schrieb im
Newsbeitrag [email protected]...
Okay Frank.. i've added the add-in, using INDIRECT.EXT
now, but i'm sorry, i can't seem to finish the formula
to
save my life right now, it's REF#'ing. i've

entered
it

exactly as such and can't see where the REF comes from:

=INDIRECT.EXT("C:\Documents and
Settings\kmcclement\Desktop'["&TEXT($B$4,"MMM dd,yyy")
&".xls]'data!C8")

Thanks again sir..
Kevin M

-----Original Message-----
Hi
download morefunc.xll (http://longre.free.fr/english)
and use the
function
INDIRECT.EXT

Note: Then you also must include the file path

--
Regards
Frank Kabel
Frankfurt, Germany

"Kevin M"

schrieb im
Newsbeitrag [email protected]...
As always, thanks Frank, do you know of a

method
for

pulling if it's closed? Sounds like what the user is
after is having her subordinates be able to enter a
date
in one sheet with nothing else open.

Kevin M

-----Original Message-----
Hi
try
=INDIRECT("'[" & TEXT(B4,"MMM dd,yyy") & ".xls]
sheet1'!
C8")

Note: This only works if the other file is opened!!
--
Regards
Frank Kabel
Frankfurt, Germany

"Kevin M"

schrieb im
Newsbeitrag [email protected]...
Hi all, TIA..
I'm trying to reference a user defined date in
B4,
it's
formatted "MMM dd, yyyy" in C8 i'm trying

to
use:

="["&$B$4&"]"&$C$8 to have it pull from another
sheet
named by date, ie: Nov 25, 2004.xls, hence why i
formatted B4 as MMM dd, yyyy. The issue is that
it
returns the date serial number instead of the
entered
text, so the answer is [38316] instead of the
text.
The
user would like to enter a date to look up and
have
Excel
return the appropriate days' responses. If
there's
an
easier way to pull this, i'm all ears!

Thanks again,
Kevin M

.


.


.


.

.