how can i use concatenate to create a linking formula?
 

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

how can i use concatenate to create a linking formula?
 

Post a sample of the formula you are trying.

Biff

"DRandolph" wrote in message
...
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

how can i use concatenate to create a linking formula?
 

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=504685

how can i use concatenate to create a linking formula?
 

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

"Biff" wrote:

Post a sample of the formula you are trying.

Biff

"DRandolph" wrote in message
...
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

how can i use concatenate to create a linking formula?
 

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

how can i use concatenate to create a linking formula?
 

Thanks!

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

"daddylonglegs" wrote:


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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=504685

how can i use concatenate to create a linking formula?
 

I'll work on the space thing tomorrow

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

"Pete" wrote:

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

how can i use concatenate to create a linking formula?
 

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

"DRandolph" wrote in message
...
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

"Biff" wrote:

Post a sample of the formula you are trying.

Biff

"DRandolph" wrote in message
...
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

how can i use concatenate to create a linking formula?
 

"DRandolph" wrote...
....
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.

how can i use concatenate to create a linking formula?
 

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

"Biff" wrote:

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

"DRandolph" wrote in message
...
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

"Biff" wrote:

Post a sample of the formula you are trying.

Biff

"DRandolph" wrote in message
...
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

how can i use concatenate to create a linking formula?
 

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

"DRandolph" wrote in message
...
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

"Biff" wrote:

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

"DRandolph" wrote in message
...
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

"Biff" wrote:

Post a sample of the formula you are trying.

Biff

"DRandolph" wrote in message
...
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