Help with Indirect

[Tanya M]

Excel 2007
Win XP Professional


Current Setup
A: Date (m/dd/yyyy)
C: Amount (needs to be pulled from the appropriate file based on the date)
F: Path (\\server\folder1\folder2\[filename part 1
G: Variable (date in mmddyy format)
H: Part III (remainder of filename]Sheet!)
I: Column ($AP) - this will be a variable also.
J: Cell ($6)

So, if I concatenate in Column K (=F5&G5&H5&I5&J5) I get
\\NTS141\KEGCommon\KE - Jasper\Inventory Projection Detail\[Inventory
Projection - KE Jasper wk092208 rev4 with KETL change.xls]Input Planned
Sales!$AP$6

What I want is the value that is in the referenced cell. I thought I was
going to need to use INDIRECT but I haven't been able to get my head around
it.

If I need to set up in a different manner or if INDIRECT is not the way to
go please advise.

Thank you in advance!

 

[Pete_UK]

INDIRECT will not work with workbooks that are closed. You might like
to download a free add-in, morefunc (do a google search), which has
the function INDIRECT.EXT which you could try instead.

Hope this helps.

Pete

 

[Tanya M]

I meant to add that I knew the function would not work with closed workbooks,
for now that is OK... I have looked into the morefunc add-in and would like
to try to avoid that for now... I would just really like to try to
understand how the indirect function is supposed to work as I cannot get it
to do what I think it is supposed to...

INDIRECT will not work with workbooks that are closed. You might like
to download a free add-in, morefunc (do a google search), which has
the function INDIRECT.EXT which you could try instead.

Hope this helps.

Pete

Excel 2007
Win XP Professional

Current Setup
A: Date (m/dd/yyyy)
C: Amount (needs to be pulled from the appropriate file based on the date)
F: Path (\\server\folder1\folder2\[filename part 1
G: Variable (date in mmddyy format)
H: Part III (remainder of filename]Sheet!)
I: Column ($AP) - this will be a variable also.
J: Cell ($6)

So, if I concatenate in Column K (=F5&G5&H5&I5&J5) I get
\\NTS141\KEGCommon\KE - Jasper\Inventory Projection Detail\[Inventory
Projection - KE Jasper wk092208 rev4 with KETL change.xls]Input Planned
Sales!$AP$6

What I want is the value that is in the referenced cell. I thought I was
going to need to use INDIRECT but I haven't been able to get my head around
it.

If I need to set up in a different manner or if INDIRECT is not the way to
go please advise.

Thank you in advance!

 

[Pete_UK]

Well, if you open that file that you are trying to get the data from
at the same time as your other file, then you could use:

=INDIRECT(K5)

assuming K5 is your concatenated reference. You will need to
incorporate apostrophes around the path, filename and sheet name to
take care of the spaces that you have, so that your formula in K5
might be:

="'"&F5&G5&H5&"'!"&I5&J5

where you should remove the ! at the end of H5.

Of course, you don't really need the full path if the file is already
open !!

Hope this helps.

Pete

I meant to add that I knew the function would not work with closed workbooks,
for now that is OK... I have looked into the morefunc add-in and would like
to try to avoid that for now...  I would just really like to try to
understand how the indirect function is supposed to work as I cannot get it
to do what I think it is supposed to...

INDIRECT will not work with workbooks that are closed. You might like
to download a free add-in, morefunc (do a google search), which has
the function INDIRECT.EXT which you could try instead.

Hope this helps.

Pete

Excel 2007
Win XP Professional
Current Setup
A:      Date (m/dd/yyyy)
C:      Amount (needs to be pulled from the appropriate file based on the date)
F:      Path (\\server\folder1\folder2\[filename part 1
G:      Variable (date in mmddyy format)
H:      Part III (remainder of filename]Sheet!)
I:      Column ($AP) - this will be a variable also.
J:      Cell ($6)
So, if I concatenate in Column K (=F5&G5&H5&I5&J5) I get
\\NTS141\KEGCommon\KE - Jasper\Inventory Projection Detail\[Inventory
Projection - KE Jasper wk092208 rev4 with KETL change.xls]Input Planned
Sales!$AP$6
What I want is the value that is in the referenced cell.  I thoughtI was
going to need to use INDIRECT but I haven't been able to get my head around
it.
If I need to set up in a different manner or if INDIRECT is not the way to
go please advise.
Thank you in advance!- Hide quoted text -

- Show quoted text -

 

[Tanya M]

I see what you're saying about the path now...(I think I've been staring at
it too long)

I did download/install morefunc and with your help and the INDIRECT.EXT
function I did get the results I wanted.

THANK YOU!

For Reference this is what I did:
A: Date (m/dd/yyyy)
C: Amount (needs to be pulled from the appropriate file based on the date)
F: Path (\\server\folder1\folder2\)
G: Filename (first part, non-variable)
H: Filename (middle part, variable)
I: Filename (last part, non-variable)
J: Worksheet name (non-variable)
K: Column (variable)
L: Cell (non-variable)

and the formula is (in C5)...
=INDIRECT.EXT("'"&F5&"["&G5&H5&I5&"]"&J5&"'!"&K5&L5)

Well, if you open that file that you are trying to get the data from
at the same time as your other file, then you could use:

=INDIRECT(K5)

assuming K5 is your concatenated reference. You will need to
incorporate apostrophes around the path, filename and sheet name to
take care of the spaces that you have, so that your formula in K5
might be:

="'"&F5&G5&H5&"'!"&I5&J5

where you should remove the ! at the end of H5.

Of course, you don't really need the full path if the file is already
open !!

Hope this helps.

Pete

I meant to add that I knew the function would not work with closed workbooks,
for now that is OK... I have looked into the morefunc add-in and would like
to try to avoid that for now... I would just really like to try to
understand how the indirect function is supposed to work as I cannot get it
to do what I think it is supposed to...

INDIRECT will not work with workbooks that are closed. You might like
to download a free add-in, morefunc (do a google search), which has
the function INDIRECT.EXT which you could try instead.

Hope this helps.

Excel 2007
Win XP Professional

Current Setup
A: Date (m/dd/yyyy)
C: Amount (needs to be pulled from the appropriate file based on the date)
F: Path (\\server\folder1\folder2\[filename part 1
G: Variable (date in mmddyy format)
H: Part III (remainder of filename]Sheet!)
I: Column ($AP) - this will be a variable also.
J: Cell ($6)

So, if I concatenate in Column K (=F5&G5&H5&I5&J5) I get
\\NTS141\KEGCommon\KE - Jasper\Inventory Projection Detail\[Inventory
Projection - KE Jasper wk092208 rev4 with KETL change.xls]Input Planned
Sales!$AP$6

What I want is the value that is in the referenced cell. I thought I was
going to need to use INDIRECT but I haven't been able to get my head around
it.

If I need to set up in a different manner or if INDIRECT is not the way to
go please advise.

Thank you in advance!- Hide quoted text -

- Show quoted text -

 

[Pete_UK]

Glad to hear that you got it to work, Tanya - thanks for feeding back.

Pete

I see what you're saying about the path now...(I think I've been staring at
it too long)

I did download/install morefunc and with your help and the INDIRECT.EXT
function I did get the results I wanted.  

THANK YOU!

For Reference this is what I did:
A:      Date (m/dd/yyyy)
C:      Amount (needs to be pulled from the appropriate file based on the date)
F:      Path (\\server\folder1\folder2\)
G:      Filename (first part, non-variable)
H:      Filename (middle part, variable)
I:      Filename (last part, non-variable)
J:      Worksheet name (non-variable)
K:      Column (variable)
L:      Cell (non-variable)

and the formula is (in C5)...
=INDIRECT.EXT("'"&F5&"["&G5&H5&I5&"]"&J5&"'!"&K5&L5)

Well, if you open that file that you are trying to get the data from
at the same time as your other file, then you could use:

assuming K5 is your concatenated reference. You will need to
incorporate apostrophes around the path, filename and sheet name to
take care of the spaces that you have, so that your formula in K5
might be:

where you should remove the ! at the end of H5.

Of course, you don't really need the full path if the file is already
open !!

Hope this helps.

Pete

I meant to add that I knew the function would not work with closed workbooks,
for now that is OK... I have looked into the morefunc add-in and would like
to try to avoid that for now...  I would just really like to try to
understand how the indirect function is supposed to work as I cannot get it
to do what I think it is supposed to...
:
INDIRECT will not work with workbooks that are closed. You might like
to download a free add-in, morefunc (do a google search), which has
the function INDIRECT.EXT which you could try instead.
Hope this helps.
Pete
Excel 2007
Win XP Professional
Current Setup
A:      Date (m/dd/yyyy)
C:      Amount (needs to be pulled from the appropriate file based on the date)
F:      Path (\\server\folder1\folder2\[filename part 1
G:      Variable (date in mmddyy format)
H:      Part III (remainder of filename]Sheet!)
I:      Column ($AP) - this will be a variable also.
J:      Cell ($6)
So, if I concatenate in Column K (=F5&G5&H5&I5&J5) I get
\\NTS141\KEGCommon\KE - Jasper\Inventory Projection Detail\[Inventory
Projection - KE Jasper wk092208 rev4 with KETL change.xls]Input Planned
Sales!$AP$6
What I want is the value that is in the referenced cell.  I thought I was
going to need to use INDIRECT but I haven't been able to get my head around
it.
If I need to set up in a different manner or if INDIRECT is not the way to
go please advise.
Thank you in advance!- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -