Inserting date variables into the paths of equations?

  • Thread starter Thread starter Graystar
  • Start date Start date
G

Graystar

I'm looking for a solution that puts Path date variables in equations?

Is it possible to take a date and insert it <more than once> into an
equation path?
such as c:\Path\2004-2 (February)\[2004-2-02 filetext.xls]

Description of Problem:
The 2nd workday in March was 3/2/04
The 2nd workday in February was 2/3/04

March is a known (and is plugged in)

What is needed is to Find the same February workday date
on-the-fly
and then insert it into the equation path
such as below:
Example: C:\path\2004-2 (February)\[2004-2-02 filetext.xls]Sheet1!<cell
reference>

Where both the "2004-2 (February)" and
the "2004-2-02" needs to be plugged into the path to pull the data.

and of course, "Sheet1!" being the sample tab label
and the cell reference to the data on that sheet.

Desired Result:
When the date of the report is plugged in, right away the data from the
previous month
is pulled and displayed.

Individual cells must be pulled for the following reason:
February has only 20 workdays
March has 23 workdays.
Otherwise I would have gone for an Array solution

It's necessary to repeat the last workday values of the previous month
(February)
in the remaining cells 21 to 23 of March's 23 workday list

Weekends and Holidays are already excluded

Help appreciated.
Will gladly share answer with those who are interested.

Thanks,
Graystar
 
Hi
I assume that the other workbook (e.g. your February workbook) is NOT
open. In this case the function INDIRECT won't work.
Alternatives:

If the other workbook is closed try the following: have a look at the
Add-In MOREFUNC.XLL
(http://longre.free.fr/english)

use the function INDIRECT.EXT.e.g. (if cell A1 stores the path
information and B1 the filename information) use:

=INDIRECT.EXT("'C:\path\" & A1 & "\["&B1&" filetext.xls]sheet1'!A1")

you may also have a look at the following thread (describing further
alternatives for accessing closed workbooks): http://tinyurl.com/2c62u
 
That's right, they are closed files.
Thanks, I'll check it out

Graystar

Frank Kabel said:
Hi
I assume that the other workbook (e.g. your February workbook) is NOT
open. In this case the function INDIRECT won't work.
Alternatives:

If the other workbook is closed try the following: have a look at the
Add-In MOREFUNC.XLL
(http://longre.free.fr/english)

use the function INDIRECT.EXT.e.g. (if cell A1 stores the path
information and B1 the filename information) use:

=INDIRECT.EXT("'C:\path\" & A1 & "\["&B1&" filetext.xls]sheet1'!A1")

you may also have a look at the following thread (describing further
alternatives for accessing closed workbooks): http://tinyurl.com/2c62u



--
Regards
Frank Kabel
Frankfurt, Germany

Graystar said:
I'm looking for a solution that puts Path date variables in equations?

Is it possible to take a date and insert it <more than once> into an
equation path?
such as c:\Path\2004-2 (February)\[2004-2-02 filetext.xls]

Description of Problem:
The 2nd workday in March was 3/2/04
The 2nd workday in February was 2/3/04

March is a known (and is plugged in)

What is needed is to Find the same February workday date
on-the-fly
and then insert it into the equation path
such as below:
Example: C:\path\2004-2 (February)\[2004-2-02 filetext.xls]Sheet1!<cell
reference>

Where both the "2004-2 (February)" and
the "2004-2-02" needs to be plugged into the path to pull the data.

and of course, "Sheet1!" being the sample tab label
and the cell reference to the data on that sheet.

Desired Result:
When the date of the report is plugged in, right away the data from the
previous month
is pulled and displayed.

Individual cells must be pulled for the following reason:
February has only 20 workdays
March has 23 workdays.
Otherwise I would have gone for an Array solution

It's necessary to repeat the last workday values of the previous month
(February)
in the remaining cells 21 to 23 of March's 23 workday list

Weekends and Holidays are already excluded

Help appreciated.
Will gladly share answer with those who are interested.

Thanks,
Graystar
Click to expand...
Click to expand...
 
Hmmm... this is a report that gets emailed to several viewers.
Special functions *might* be a problem, but I'll still check it. Thanks

Frank Kabel said:
Hi
I assume that the other workbook (e.g. your February workbook) is NOT
open. In this case the function INDIRECT won't work.
Alternatives:

If the other workbook is closed try the following: have a look at the
Add-In MOREFUNC.XLL
(http://longre.free.fr/english)

use the function INDIRECT.EXT.e.g. (if cell A1 stores the path
information and B1 the filename information) use:

=INDIRECT.EXT("'C:\path\" & A1 & "\["&B1&" filetext.xls]sheet1'!A1")

you may also have a look at the following thread (describing further
alternatives for accessing closed workbooks): http://tinyurl.com/2c62u



--
Regards
Frank Kabel
Frankfurt, Germany

Graystar said:
I'm looking for a solution that puts Path date variables in equations?

Is it possible to take a date and insert it <more than once> into an
equation path?
such as c:\Path\2004-2 (February)\[2004-2-02 filetext.xls]

Description of Problem:
The 2nd workday in March was 3/2/04
The 2nd workday in February was 2/3/04

March is a known (and is plugged in)

What is needed is to Find the same February workday date
on-the-fly
and then insert it into the equation path
such as below:
Example: C:\path\2004-2 (February)\[2004-2-02 filetext.xls]Sheet1!<cell
reference>

Where both the "2004-2 (February)" and
the "2004-2-02" needs to be plugged into the path to pull the data.

and of course, "Sheet1!" being the sample tab label
and the cell reference to the data on that sheet.

Desired Result:
When the date of the report is plugged in, right away the data from the
previous month
is pulled and displayed.

Individual cells must be pulled for the following reason:
February has only 20 workdays
March has 23 workdays.
Otherwise I would have gone for an Array solution

It's necessary to repeat the last workday values of the previous month
(February)
in the remaining cells 21 to 23 of March's 23 workday list

Weekends and Holidays are already excluded

Help appreciated.
Will gladly share answer with those who are interested.

Thanks,
Graystar
Click to expand...
Click to expand...
 
Back
Top