=[test.xls]sheet1!A1

Is it possible to make the "sheet1" portion of this formula tied to a cell
in the destination workbook so that if I type "sheet2" in cell A3 of my
destination sheet, it will show what is in cell A1 of =[test.xls]sheet2!A1

What I am trying to do is make all the references in my workbook dependent
on what sheet# I have typed into cell A3. Up till now I have been fetching
all the data based on a 4 digit date format such as "0513". "0513" would be
the name of a sheet that was saved with data for may 13th. My hope is to be
able to type 0513 into cell A3 and have it reference the data. Can this be
done or is there a better way?

Also I noticed that in order to update the formula cell I have to select the
test workbook in the dialog box each time. Is there a way to make this a
constant reference instead of having to ok it? Hopefully without having to
open the reference workbook each time either?

I am rather new to this...

--
Thank you!

The function you'd want to use is =indirect().

But =indirect() won't work if the sending file is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

===
If you have trouble getting to the site, then search google for indirect.ext.

I found this alternative site:
http://download.cnet.com/Morefunc/30...-10423159.html

I didn't look to see if it was the most current version.

I'd check the original site every so often to see if it's working.

Doug wrote:

=[test.xls]sheet1!A1

Is it possible to make the "sheet1" portion of this formula tied to a cell
in the destination workbook so that if I type "sheet2" in cell A3 of my
destination sheet, it will show what is in cell A1 of =[test.xls]sheet2!A1

What I am trying to do is make all the references in my workbook dependent
on what sheet# I have typed into cell A3. Up till now I have been fetching
all the data based on a 4 digit date format such as "0513". "0513" would be
the name of a sheet that was saved with data for may 13th. My hope is to be
able to type 0513 into cell A3 and have it reference the data. Can this be
done or is there a better way?

Also I noticed that in order to update the formula cell I have to select the
test workbook in the dialog box each time. Is there a way to make this a
constant reference instead of having to ok it? Hopefully without having to
open the reference workbook each time either?

I am rather new to this...

--
Thank you!

--

Dave Peterson

Unfortunately, no. The closest thing is the INDIRECT function, but its
doesn't work on closed external workbooks. IF you're open to add-ins, take a
look he
http://www.download.com/Morefunc/300...-10423159.html

With the MoreFunc add-in, you can use the INDIRECT.EXT function, which would
allow you to do this.
--
Best Regards,

Luke M
"Doug" wrote in message
...
=[test.xls]sheet1!A1

Is it possible to make the "sheet1" portion of this formula tied to a cell
in the destination workbook so that if I type "sheet2" in cell A3 of my
destination sheet, it will show what is in cell A1 of =[test.xls]sheet2!A1

What I am trying to do is make all the references in my workbook dependent
on what sheet# I have typed into cell A3. Up till now I have been fetching
all the data based on a 4 digit date format such as "0513". "0513" would
be
the name of a sheet that was saved with data for may 13th. My hope is to
be
able to type 0513 into cell A3 and have it reference the data. Can this be
done or is there a better way?

Also I noticed that in order to update the formula cell I have to select
the
test workbook in the dialog box each time. Is there a way to make this a
constant reference instead of having to ok it? Hopefully without having to
open the reference workbook each time either?

I am rather new to this...

--
Thank you!

Thank you very much!
How would I need to type this in? This is what I have and it gives me an
error "A formula in this worksheet contains one or more invalid references."

='C:\Users\The
King''s\Desktop\[test.xlsx]INDIRECT.EXT([book2.xlsx]sheet1'!C1)'!A1
--
Thank you!


"Luke M" wrote:

Unfortunately, no. The closest thing is the INDIRECT function, but its
doesn't work on closed external workbooks. IF you're open to add-ins, take a
look he
http://www.download.com/Morefunc/300...-10423159.html

With the MoreFunc add-in, you can use the INDIRECT.EXT function, which would
allow you to do this.
--
Best Regards,

Luke M
"Doug" wrote in message
...
=[test.xls]sheet1!A1

Is it possible to make the "sheet1" portion of this formula tied to a cell
in the destination workbook so that if I type "sheet2" in cell A3 of my
destination sheet, it will show what is in cell A1 of =[test.xls]sheet2!A1

What I am trying to do is make all the references in my workbook dependent
on what sheet# I have typed into cell A3. Up till now I have been fetching
all the data based on a 4 digit date format such as "0513". "0513" would
be
the name of a sheet that was saved with data for may 13th. My hope is to
be
able to type 0513 into cell A3 and have it reference the data. Can this be
done or is there a better way?

Also I noticed that in order to update the formula cell I have to select
the
test workbook in the dialog box each time. Is there a way to make this a
constant reference instead of having to ok it? Hopefully without having to
open the reference workbook each time either?

I am rather new to this...

--
Thank you!


.

You downloaded and installed the morefunc addin, right?

If A3 contains the sheet name (0513 as text!) and that A3 is on the same sheet
as the cell with the formula:

=indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]" & A3 &"'!A1")

if A3 is on a different sheet but the same workbook as the cell with the
formula:

=indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]"
& 'sheet 99'!A3 &"'!A1")

If A3 contains the value 513, you'll need to format the value nicely:

=indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]"
& text('sheet 99'!A3,"0000") &"'!A1")

If A3 contains a date (say May 13, 2010):

=indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]"
& text('sheet 99'!A3,"mmdd") &"'!A1")

(all untested. watch for typos.)

Doug wrote:

Thank you very much!
How would I need to type this in? This is what I have and it gives me an
error "A formula in this worksheet contains one or more invalid references."

='C:\Users\The
King''s\Desktop\[test.xlsx]INDIRECT.EXT([book2.xlsx]sheet1'!C1)'!A1
--
Thank you!

"Luke M" wrote:

Unfortunately, no. The closest thing is the INDIRECT function, but its
doesn't work on closed external workbooks. IF you're open to add-ins, take a
look he
http://www.download.com/Morefunc/300...-10423159.html

With the MoreFunc add-in, you can use the INDIRECT.EXT function, which would
allow you to do this.
--
Best Regards,

Luke M
"Doug" wrote in message
...
=[test.xls]sheet1!A1

Is it possible to make the "sheet1" portion of this formula tied to a cell
in the destination workbook so that if I type "sheet2" in cell A3 of my
destination sheet, it will show what is in cell A1 of =[test.xls]sheet2!A1

What I am trying to do is make all the references in my workbook dependent
on what sheet# I have typed into cell A3. Up till now I have been fetching
all the data based on a 4 digit date format such as "0513". "0513" would
be
the name of a sheet that was saved with data for may 13th. My hope is to
be
able to type 0513 into cell A3 and have it reference the data. Can this be
done or is there a better way?

Also I noticed that in order to update the formula cell I have to select
the
test workbook in the dialog box each time. Is there a way to make this a
constant reference instead of having to ok it? Hopefully without having to
open the reference workbook each time either?

I am rather new to this...

--
Thank you!


.


--

Dave Peterson

=INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&"text('sheet
1'!A3)"&"'!A1")
I am sorry! It is giving me a #ref! response with the above and if I put
A1",,,,) at the end it gives me #value!

--
Thank you!


"Doug" wrote:

=[test.xls]sheet1!A1

Is it possible to make the "sheet1" portion of this formula tied to a cell
in the destination workbook so that if I type "sheet2" in cell A3 of my
destination sheet, it will show what is in cell A1 of =[test.xls]sheet2!A1

What I am trying to do is make all the references in my workbook dependent
on what sheet# I have typed into cell A3. Up till now I have been fetching
all the data based on a 4 digit date format such as "0513". "0513" would be
the name of a sheet that was saved with data for may 13th. My hope is to be
able to type 0513 into cell A3 and have it reference the data. Can this be
done or is there a better way?

Also I noticed that in order to update the formula cell I have to select the
test workbook in the dialog box each time. Is there a way to make this a
constant reference instead of having to ok it? Hopefully without having to
open the reference workbook each time either?

I am rather new to this...

--
Thank you!

What's in A3 of sheet1?

If it's text (like '0513), then use:

=indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]"
& 'sheet1'!A3 &"'!A1")

if it's a number:

=indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]"
& text('Sheet1'!A3,"0000") &"'!A1")

Watch those " marks. Don't surround everything with them!

Doug wrote:

=INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&"text('sheet
1'!A3)"&"'!A1")
I am sorry! It is giving me a #ref! response with the above and if I put
A1",,,,) at the end it gives me #value!

--
Thank you!

"Doug" wrote:

=[test.xls]sheet1!A1

Is it possible to make the "sheet1" portion of this formula tied to a cell
in the destination workbook so that if I type "sheet2" in cell A3 of my
destination sheet, it will show what is in cell A1 of =[test.xls]sheet2!A1

What I am trying to do is make all the references in my workbook dependent
on what sheet# I have typed into cell A3. Up till now I have been fetching
all the data based on a 4 digit date format such as "0513". "0513" would be
the name of a sheet that was saved with data for may 13th. My hope is to be
able to type 0513 into cell A3 and have it reference the data. Can this be
done or is there a better way?

Also I noticed that in order to update the formula cell I have to select the
test workbook in the dialog box each time. Is there a way to make this a
constant reference instead of having to ok it? Hopefully without having to
open the reference workbook each time either?

I am rather new to this...

--
Thank you!

--

Dave Peterson

I just found that one of my problems is that it is still not reading it when
the workbook is closed, but as soon as I open the workbook it changes
from#REF! to the cell value. I have the add-in installed and the workbook
recognizes the function indirect.ext so I am not sure what the problem could
be?

Any more suggestions?

--
Thank you!


"Dave Peterson" wrote:

What's in A3 of sheet1?

If it's text (like '0513), then use:

=indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]"
& 'sheet1'!A3 &"'!A1")

if it's a number:

=indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]"
& text('Sheet1'!A3,"0000") &"'!A1")

Watch those " marks. Don't surround everything with them!

Doug wrote:

=INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&"text('sheet
1'!A3)"&"'!A1")
I am sorry! It is giving me a #ref! response with the above and if I put
A1",,,,) at the end it gives me #value!

--
Thank you!

"Doug" wrote:

=[test.xls]sheet1!A1

Is it possible to make the "sheet1" portion of this formula tied to a cell
in the destination workbook so that if I type "sheet2" in cell A3 of my
destination sheet, it will show what is in cell A1 of =[test.xls]sheet2!A1

What I am trying to do is make all the references in my workbook dependent
on what sheet# I have typed into cell A3. Up till now I have been fetching
all the data based on a 4 digit date format such as "0513". "0513" would be
the name of a sheet that was saved with data for may 13th. My hope is to be
able to type 0513 into cell A3 and have it reference the data. Can this be
done or is there a better way?

Also I noticed that in order to update the formula cell I have to select the
test workbook in the dialog box each time. Is there a way to make this a
constant reference instead of having to ok it? Hopefully without having to
open the reference workbook each time either?

I am rather new to this...

--
Thank you!

--

Dave Peterson
.

Time to share the formula you used and all the values in each of the cells that
that formula uses.



Doug wrote:

I just found that one of my problems is that it is still not reading it when
the workbook is closed, but as soon as I open the workbook it changes
from#REF! to the cell value. I have the add-in installed and the workbook
recognizes the function indirect.ext so I am not sure what the problem could
be?

Any more suggestions?

--
Thank you!

"Dave Peterson" wrote:

What's in A3 of sheet1?

If it's text (like '0513), then use:

=indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]"
& 'sheet1'!A3 &"'!A1")

if it's a number:

=indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]"
& text('Sheet1'!A3,"0000") &"'!A1")

Watch those " marks. Don't surround everything with them!

Doug wrote:

=INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&"text('sheet
1'!A3)"&"'!A1")
I am sorry! It is giving me a #ref! response with the above and if I put
A1",,,,) at the end it gives me #value!

--
Thank you!

"Doug" wrote:

=[test.xls]sheet1!A1

Is it possible to make the "sheet1" portion of this formula tied to a cell
in the destination workbook so that if I type "sheet2" in cell A3 of my
destination sheet, it will show what is in cell A1 of =[test.xls]sheet2!A1

What I am trying to do is make all the references in my workbook dependent
on what sheet# I have typed into cell A3. Up till now I have been fetching
all the data based on a 4 digit date format such as "0513". "0513" would be
the name of a sheet that was saved with data for may 13th. My hope is to be
able to type 0513 into cell A3 and have it reference the data. Can this be
done or is there a better way?

Also I noticed that in order to update the formula cell I have to select the
test workbook in the dialog box each time. Is there a way to make this a
constant reference instead of having to ok it? Hopefully without having to
open the reference workbook each time either?

I am rather new to this...

--
Thank you!

--

Dave Peterson
.


--

Dave Peterson

Ok, I will give you all the details. Thank you for your time and sorry for
the length of this. Let me start over and explain everything...

Here is the formula that I have been using referencing a sheet in my
workbook. These cover an array of R3:DJ504 just to give you an idea how many
lookups I am currently using. These are mostly for # values.
=IF(ISERROR(VLOOKUP(O3,Import!P:CB,18,FALSE)),"",V LOOKUP(O3,Import!P:CB,18,FALSE))
Now that I am wanting to reference a fetch file instead of a sheet in my
workbook I would like to change this. In cell M1 of my workbook I have a
drop down menu of dates such as "10-May-10" in text format. I am wanting to
figure out the most efficient way to use this as a reference variable to
sheet names in my fetch file. I am hopeful to do this with the fetch file
closed so that I will never need to open it unless I find something doesn't
make sense with the data I am seeing.
I downloaded and installed the add-in for indirect.ext and it was still
giving #Ref! when the book is closed. As a test apart from the formula above,
I tried =INDIRECT("'[test.xlsx]"& Sheet1!A4 &"'!A1") &
=INDIRECT.EXT("'[test.xlsx]"&Sheet1!A4&"'!A1") only difference being ".ext".
They both work with the workbook open, but not closed.
I was reading an article about Harlan's Pull function and am wondering if
this would be a good alternative. It aparently works whether the fetch file
is open or closed and suppose to work with vlookups, but again I can't seem
to figure out how the formula should be structured. Do you have any more
questions to adaquately address my problem? What ever you think is best?

--
Thank you!


"Dave Peterson" wrote:

Time to share the formula you used and all the values in each of the cells that
that formula uses.



Doug wrote:

I just found that one of my problems is that it is still not reading it when
the workbook is closed, but as soon as I open the workbook it changes
from#REF! to the cell value. I have the add-in installed and the workbook
recognizes the function indirect.ext so I am not sure what the problem could
be?

Any more suggestions?

--
Thank you!

"Dave Peterson" wrote:

What's in A3 of sheet1?

If it's text (like '0513), then use:

=indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]"
& 'sheet1'!A3 &"'!A1")

if it's a number:

=indirect.ext("'C:\Users\The King''s\Desktop\[test.xlsx]"
& text('Sheet1'!A3,"0000") &"'!A1")

Watch those " marks. Don't surround everything with them!

Doug wrote:

=INDIRECT.EXT("'C:\Users\The King''s\Desktop\[test.xlsx]"&"text('sheet
1'!A3)"&"'!A1")
I am sorry! It is giving me a #ref! response with the above and if I put
A1",,,,) at the end it gives me #value!

--
Thank you!

"Doug" wrote:

=[test.xls]sheet1!A1

Is it possible to make the "sheet1" portion of this formula tied to a cell
in the destination workbook so that if I type "sheet2" in cell A3 of my
destination sheet, it will show what is in cell A1 of =[test.xls]sheet2!A1

What I am trying to do is make all the references in my workbook dependent
on what sheet# I have typed into cell A3. Up till now I have been fetching
all the data based on a 4 digit date format such as "0513". "0513" would be
the name of a sheet that was saved with data for may 13th. My hope is to be
able to type 0513 into cell A3 and have it reference the data. Can this be
done or is there a better way?

Also I noticed that in order to update the formula cell I have to select the
test workbook in the dialog box each time. Is there a way to make this a
constant reference instead of having to ok it? Hopefully without having to
open the reference workbook each time either?

I am rather new to this...

--
Thank you!

--

Dave Peterson
.


--

Dave Peterson
.

try indirect function

all the best