how can i use concatenate to create a linking formula?

[DRandolph]

I receive daily reports from the people that work for me. The files are
saved in a known location and a known naming structure (initials - date.xls)

In a summary report I CONCATENATE their initials and the date along with a
path but cannot get the INDIRECT command to recognize the text line.

Any help would be greatly appreciated.

David

 

[Biff]

Post a sample of the formula you are trying.

Biff

 

[daddylonglegs]

If you're concatenating dates then you need to use something like

=TEXT(A1,"mm/dd/yy") in your formula otherwise you'll just get the date
serial number

 

[DRandolph]

I use a text column 01 to 30 (xx) to count off the days and bring everything
else together. The initials (yy) also change.

'='H:\06-Stuff\Work\Opps\[BM - OE Log - 01-01-06.xls]Sheet2'!$E$18

-------------------------------yy------------------xx-------------------------

The full formula has an IFERR in it, but this is the meat of it all.

David

 

[Pete]

If the sheet name has spaces in it you will have to wrap the
path/filename and sheet name with apostrophes.

INDIRECT( ) can only work with files that are open, so this might be
another problem.

Hope this helps.

Pete

 

[DRandolph]

Thanks!

I'm actuall using a 01 text string to create my own date string.

 

[DRandolph]

I'll work on the space thing tomorrow

Right now I can't get INDIRECT to work across sheets in the same workbook .

 

[Biff]

Ok, now I'm not really following you?

'='H:\06-Stuff\Work\Opps\[BM - OE Log - 01-01-06.xls]Sheet2'!$E$18

Do you want to use a cell reference for "BM" (employee initials) and a cell
reference for "01" (day of month)?

A downside of using Indirect is that it requires the other file be open, as
Pete noted.

Biff

I use a text column 01 to 30 (xx) to count off the days and bring
everything
else together. The initials (yy) also change.

'='H:\06-Stuff\Work\Opps\[BM - OE Log - 01-01-06.xls]Sheet2'!$E$18

-------------------------------yy------------------xx-------------------------

The full formula has an IFERR in it, but this is the meat of it all.

David

Post a sample of the formula you are trying.

Biff

 

[Harlan Grove]

Right now I can't get INDIRECT to work across sheets in the same workbook .

....

This is usually due to spaces in worksheet names. You have to have *EXACTLY*
the same spaces in INDIRECT's argument, and the worksheet name needs to be
inside single quotes.

 

[DRandolph]

Yes, I am using cell referances for initials and day of month. That part
works fine and I can create the text line (see sample below) fine. If I type
that line into a cell it works fine. I want to automate the whole process
and was hoping INDIRECT was the answer... BUT if the other sheets have to be
open, it is useless. There are 6 daily reports and 20 working days per
month, so I would need 121 file open to review month end numbers.

Any ideas besides INDIRECT or hiring a programer?

David

Ok, now I'm not really following you?

'='H:\06-Stuff\Work\Opps\[BM - OE Log - 01-01-06.xls]Sheet2'!$E$18

Do you want to use a cell reference for "BM" (employee initials) and a cell
reference for "01" (day of month)?

A downside of using Indirect is that it requires the other file be open, as
Pete noted.

Biff

I use a text column 01 to 30 (xx) to count off the days and bring
everything
else together. The initials (yy) also change.

'='H:\06-Stuff\Work\Opps\[BM - OE Log - 01-01-06.xls]Sheet2'!$E$18

-------------------------------yy------------------xx-------------------------

The full formula has an IFERR in it, but this is the meat of it all.

David

Post a sample of the formula you are trying.

Biff

I receive daily reports from the people that work for me. The files are
saved in a known location and a known naming structure (initials -
date.xls)

In a summary report I CONCATENATE their initials and the date along
with a
path but cannot get the INDIRECT command to recognize the text line.

Any help would be greatly appreciated.

David

 

[Biff]

BUT if the other sheets have to be open, it is useless

That's usually the reaction when people find out the limitation of using
Indirect.

Any ideas besides INDIRECT or hiring a programer?

Although I've never used it, there is a VBA based utility that is supposed
to do this sort of thing. It's called PULL and the author is Harlan Grove.
You should be able to find it by doing a search. I don't have any links,
sorry.

Biff

Yes, I am using cell referances for initials and day of month. That part
works fine and I can create the text line (see sample below) fine. If I
type
that line into a cell it works fine. I want to automate the whole process
and was hoping INDIRECT was the answer... BUT if the other sheets have to
be
open, it is useless. There are 6 daily reports and 20 working days per
month, so I would need 121 file open to review month end numbers.

Any ideas besides INDIRECT or hiring a programer?

David

Ok, now I'm not really following you?

'='H:\06-Stuff\Work\Opps\[BM - OE Log - 01-01-06.xls]Sheet2'!$E$18

Do you want to use a cell reference for "BM" (employee initials) and a
cell
reference for "01" (day of month)?

A downside of using Indirect is that it requires the other file be open,
as
Pete noted.

Biff

I use a text column 01 to 30 (xx) to count off the days and bring
everything
else together. The initials (yy) also change.

'='H:\06-Stuff\Work\Opps\[BM - OE Log - 01-01-06.xls]Sheet2'!$E$18

-------------------------------yy------------------xx-------------------------

The full formula has an IFERR in it, but this is the meat of it all.

David

:

Post a sample of the formula you are trying.

Biff

I receive daily reports from the people that work for me. The files
are
saved in a known location and a known naming structure (initials -
date.xls)

In a summary report I CONCATENATE their initials and the date along
with a
path but cannot get the INDIRECT command to recognize the text line.

Any help would be greatly appreciated.

David