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.
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 said:
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.
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
