Yesteday I got some tips to use indirect funcion but keep getting error
messages. I am summarizing 36 accounts, for all clarity they are all separate
files. I would like to create a reference so I can copy the cell by pulling
it down and excell will automatically number up the certificates. Below you
can find the reference as it is now, when copying it now it keep on repeating
the certificate 1 and I have to change it by hand.

='C:\Documents and Settings\Willemv\My Documents\04H0607 -
RAMP\I-DRIVE\Accounts\Mdutjana\[MDUTJANA CERTIFICATE 1.xls]VAT'!$Q$28

We can't see how many spaces you have in your file path, but change
='C:\Documents and Settings\Willemv\My Documents\04H0607 -
RAMP\I-DRIVE\Accounts\Mdutjana\[MDUTJANA CERTIFICATE 1.xls]VAT'!$Q$28 to
="'C:\Documents and Settings\Willemv\My Documents\04H0607 -
RAMP\I-DRIVE\Accounts\Mdutjana\[MDUTJANA CERTIFICATE
"&ROW(A1)&".xls]VAT'!$Q$28"
--
David Biddulph

"willem" wrote in message
...
Yesteday I got some tips to use indirect funcion but keep getting error
messages. I am summarizing 36 accounts, for all clarity they are all
separate
files. I would like to create a reference so I can copy the cell by
pulling
it down and excell will automatically number up the certificates. Below
you
can find the reference as it is now, when copying it now it keep on
repeating
the certificate 1 and I have to change it by hand.

='C:\Documents and Settings\Willemv\My Documents\04H0607 -
RAMP\I-DRIVE\Accounts\Mdutjana\[MDUTJANA CERTIFICATE 1.xls]VAT'!$Q$28

I should, of course, have said
=INDIRECT("'C:\Documents and Settings\Willemv\My Documents\04H0607 -
RAMP\I-DRIVE\Accounts\Mdutjana\[MDUTJANA CERTIFICATE " & ROW(A1) &
".xls]VAT'!$Q$28")
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
We can't see how many spaces you have in your file path, but change
='C:\Documents and Settings\Willemv\My Documents\04H0607 -
RAMP\I-DRIVE\Accounts\Mdutjana\[MDUTJANA CERTIFICATE 1.xls]VAT'!$Q$28 to
="'C:\Documents and Settings\Willemv\My Documents\04H0607 -
RAMP\I-DRIVE\Accounts\Mdutjana\[MDUTJANA CERTIFICATE
"&ROW(A1)&".xls]VAT'!$Q$28"
--
David Biddulph

"willem" wrote in message
...
Yesteday I got some tips to use indirect funcion but keep getting error
messages. I am summarizing 36 accounts, for all clarity they are all
separate
files. I would like to create a reference so I can copy the cell by
pulling
it down and excell will automatically number up the certificates. Below
you
can find the reference as it is now, when copying it now it keep on
repeating
the certificate 1 and I have to change it by hand.

='C:\Documents and Settings\Willemv\My Documents\04H0607 -
RAMP\I-DRIVE\Accounts\Mdutjana\[MDUTJANA CERTIFICATE 1.xls]VAT'!$Q$28

If you're going to use =indirect() then the "sending" workbooks will have to be
open--or you'll get an error back.

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

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



willem wrote:

Yesteday I got some tips to use indirect funcion but keep getting error
messages. I am summarizing 36 accounts, for all clarity they are all separate
files. I would like to create a reference so I can copy the cell by pulling
it down and excell will automatically number up the certificates. Below you
can find the reference as it is now, when copying it now it keep on repeating
the certificate 1 and I have to change it by hand.

='C:\Documents and Settings\Willemv\My Documents\04H0607 -
RAMP\I-DRIVE\Accounts\Mdutjana\[MDUTJANA CERTIFICATE 1.xls]VAT'!$Q$28

--

Dave Peterson

That is correct dave, is there any other solution?
I got the cells working now but as soon as files are closed and opened they
appear or dissapear. Is there any way to save the value's one's it's got it?

"Dave Peterson" wrote:

If you're going to use =indirect() then the "sending" workbooks will have to be
open--or you'll get an error back.

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

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



willem wrote:

Yesteday I got some tips to use indirect funcion but keep getting error
messages. I am summarizing 36 accounts, for all clarity they are all separate
files. I would like to create a reference so I can copy the cell by pulling
it down and excell will automatically number up the certificates. Below you
can find the reference as it is now, when copying it now it keep on repeating
the certificate 1 and I have to change it by hand.

='C:\Documents and Settings\Willemv\My Documents\04H0607 -
RAMP\I-DRIVE\Accounts\Mdutjana\[MDUTJANA CERTIFICATE 1.xls]VAT'!$Q$28

--

Dave Peterson

If I'm setting this kind of thing up and it won't change, I'll build the
formulas as strings. Kind of like:

="$$$'C:\yourlongpathhere\[MDUTJANA CERTIFICATE " & row()-1 & ".xls]VAT'!$Q$28"

Modify the row()-1 to start with 1.

So if you put the first formula in row 98 and want to retrieve data from
....certificate 1.xls, then it would be row()-97.

Then drag down as far as I need.

Then my formulas result in things that look like formulas:

$$$'C:\yourlongpathhere\[MDUTJANA CERTIFICATE 1.xls]VAT'!$Q$28
$$$'C:\yourlongpathhere\[MDUTJANA CERTIFICATE 2.xls]VAT'!$Q$28
$$$'C:\yourlongpathhere\[MDUTJANA CERTIFICATE 3.xls]VAT'!$Q$28
$$$'C:\yourlongpathhere\[MDUTJANA CERTIFICATE 4.xls]VAT'!$Q$28
$$$'C:\yourlongpathhere\[MDUTJANA CERTIFICATE 5.xls]VAT'!$Q$28

Then I convert it to values
Select the range
edit|copy followed by edit|paste special|Values

But here comes the dangerous part...

I convert the strings to real formulas.
Select the range
Edit|Replace
what: $$$
with: =
replace all

This is very important!!!!

Do this against a couple of cells first.

If you made a mistake in your formula, then you'll be prompted to look for the
real sending file. If you have 2 prompts, no big deal. If you have 100's or
1000's, you'll be dismissing lots and lots of dialogs or killing excel via
alt-ctrl-delete.




willem wrote:

That is correct dave, is there any other solution?
I got the cells working now but as soon as files are closed and opened they
appear or dissapear. Is there any way to save the value's one's it's got it?

"Dave Peterson" wrote:

If you're going to use =indirect() then the "sending" workbooks will have to be
open--or you'll get an error back.

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

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



willem wrote:

Yesteday I got some tips to use indirect funcion but keep getting error
messages. I am summarizing 36 accounts, for all clarity they are all separate
files. I would like to create a reference so I can copy the cell by pulling
it down and excell will automatically number up the certificates. Below you
can find the reference as it is now, when copying it now it keep on repeating
the certificate 1 and I have to change it by hand.

='C:\Documents and Settings\Willemv\My Documents\04H0607 -
RAMP\I-DRIVE\Accounts\Mdutjana\[MDUTJANA CERTIFICATE 1.xls]VAT'!$Q$28

--

Dave Peterson


--

Dave Peterson

Thank for the link dave or who ever put it up here.

The link gives a download for extra functions. INDIRECT.EXE works like
indirect but the worksheets can be closed.

Yeehah, did saves me a ****load of work.

"Dave Peterson" wrote:

If you're going to use =indirect() then the "sending" workbooks will have to be
open--or you'll get an error back.

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

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



willem wrote:

Yesteday I got some tips to use indirect funcion but keep getting error
messages. I am summarizing 36 accounts, for all clarity they are all separate
files. I would like to create a reference so I can copy the cell by pulling
it down and excell will automatically number up the certificates. Below you
can find the reference as it is now, when copying it now it keep on repeating
the certificate 1 and I have to change it by hand.

='C:\Documents and Settings\Willemv\My Documents\04H0607 -
RAMP\I-DRIVE\Accounts\Mdutjana\[MDUTJANA CERTIFICATE 1.xls]VAT'!$Q$28

--

Dave Peterson