Advanced Worksheet Formulas: External References (Excel 2000)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Here is my problem:
I have a datasheet with funds on each row, that must take values from an
external sheets into different columns for each row.

It works like this, a different fund on each row, with several classes (for
different currencies of the same fund). We have external source sheets for
each fund, so each row of the main sheet must point to a different source
sheet.

Is that possible? That means I need to point the links for each row at a
different source sheet, preferably using parameters. This is so that it's
essentially the same link on each row, with just the fund name being
different, and the columns obviously point to different source columsn with
values for the different classes.

Now all that wouldn't be so bad, but unfortunately it's more complex. In the
main sheet the classes are named Class 1, Class 2, Class 3, Class 4 up to
Class 8, but not all of them will be enabled as some classes are not live.
And the trouble is, it won't be that Class 1, 2, 3 are enabled, it may be
that Classes 1, 2, 4, 6 are enabled.

And in the source sheet (where the data will come from) ONLY data fro the
enabled classes will be output from the source system.

So how can I link to the source shet=et using an offset, and also use
paremeters or variables in the formula so they can essentially all be the
same.

Would a formula using ADRESS or INDIRECT work?

I know that I could do this with a VBA macro, but we want to avoid that as a
macro will have to be tested much more.

thanks for any help

Philip
 
Hi
are the other sheets in different files or within the same file. For
the latter INDIRECT should work. You could combine this with ISERROR
for example.
To give you an example:
- column A on your main sheet is the fund name (e.g. 'fund 1'). This
name is identical to the sheet name
- column B on your main sheet stores the class name. e.g. 'Class 1'.
This name is identical to the column heading in row one of your
individual fund sheets
- I assume you want the value from row 2 on these individual fund
sheets.

Try the following formula in C1 on your main sheet (A1: fund name, B1
class name):
=IF(ISERROR(INDIRECT("'" & A1 $ "'!A1")),"Fund sheet does not
exist",IF(ISNA(MATCH(B1,INDIRECT("'" & A1 $ "'!A1:X1"),0)),"Class not
active",INDEX(INDIRECT("'" & A1 $ "'!A2:X2"),1,MATCH(B1,INDIRECT("'" &
A1 $ "'!A1:X1")))))
 
Hi,

thanks Frank.

But where would I put the source worksheet filename and path. The source
data will always be in 100 seperate workbooks, one for each fund, with the
fund in say, column A, and the classes in Columns B, C, D etc...

thanks for your help

Philip
 
Hi
as said this procdure works only within ONE workbook. INDIRECT won't
work on closed workbooks. there're some alternatives but using that on
100 separate workbooks it will be horrible slow (at least I would
assume that).
This sound more like a database application and not like an Excel
application!
 
Philip said:
Is that possible? That means I need to point the links for each row at a
different source sheet, preferably using parameters. This is so that it's
essentially the same link on each row, with just the fund name being
different, and the columns obviously point to different source columsn with
values for the different classes. ....
Would a formula using ADRESS or INDIRECT work?

I know that I could do this with a VBA macro, but we want to avoid that as
a macro will have to be tested much more.
Click to expand...

First see

http://google.com/[email protected]

If you're referencing closed workbooks, the alternatives in that post are
all there are.

As for adjusting ranges, you could use ADDRESS with suitable arguments. But
INDIRECT won't be any help unless your files are open.

As for VBA, it's not absolutely necessary, but it's unavoidable in some
situations, and testing isn't really all that hard.
 
Frank Kabel said:
as said this procdure works only within ONE workbook. INDIRECT won't
work on closed workbooks. there're some alternatives but using that on
100 separate workbooks it will be horrible slow (at least I would
assume that).
Click to expand...

Nevertheless, it'd be faster than collecting the information manually, which
appears to be what you're implying the OP should do since you don't deign to
offer those alternatives.
This sound more like a database application and not like an Excel
application!
Click to expand...

And if the OP or the OP's users don't have database software? And if the
data is in nontabular Excel files to begin with?

To the extent consolidated data could be used in *subsequent* calculations
more involved that counting, summing or averaging, it's arguable whether the
OP's task would be better suited to spreadsheet or database.
 
Harlan Grove said:
Nevertheless, it'd be faster than collecting the information manually, which
appears to be what you're implying the OP should do since you don't deign to
offer those alternatives.
Click to expand...

Just forgot to add the link :-)
http://tinyurl.com/2c62u

And for the OP: In your case probably INIRECT.EXT would work:

=IF(ISERROR(INDIRECT.EXT("'" & A1 & "[" & B1 & "]" & C1 &
"'!A1")),"Fund sheet does not
exist",IF(ISNA(MATCH(B1,INDIRECT.EXT("'" & A1 & "[" & B1 & "]" & C1 &
"'!A1:X1"),0)),"Class not
active",INDEX(INDIRECT.EXT("'" & A1 & "[" & B1 & "]" & C1 &
"'!A2:X2"),1,MATCH(B1,INDIRECT("'" & A1 & "[" & B1 & "]" & C1 &
"'!A1:X1")))))

Assumptions:
column A: path (e.g. C:\temp\)
column B: filename (e.g. test.xls)
column C: sheetname (e.g. fund1)
column D: class name


Just test it :-)


And if the OP or the OP's users don't have database software? And if the
data is in nontabular Excel files to begin with?

To the extent consolidated data could be used in *subsequent* calculations
more involved that counting, summing or averaging, it's arguable whether the
OP's task would be better suited to spreadsheet or database.
Click to expand...

In this case I just made some simple assumptions:
- he is talking about funds with several classes (currencies)
- I would expect that each sheet contains either transaction/pricing
data, or static data for specific funds
- we're talking about > 100 funds
- at least from the description I got the 'feeling' that the data on
each sheet is ordered in a database like structure -> but of course
this may be wrong

I know that several (even large banks) are using Excel for managing
funds, etc BUT this is not the right tool for storing this kind of
data. This should be stored in a database like application. and if the
OP does not have a database system he should really consider buying one
:-)

I agree with you that if you want to calculate with the data you may
use Excel for this (and not a database) but still use a central storage
system.

Frank
 
Hi,

Yes the other sheets containing the source data for funds (by class) are in
different sheets, one per fund.

And the sourceworkbooks would not be open...

So how would I do that with INDIRECT and ISERROR ?

I agree that testing a VBA macro is not hard and writing one to do this
would be a snap, but we're against the clock (serious time issue before we
HAVE to deliver) so we hope to do it without if at all possible, so long as
it's going to be scaleable and manageable...

And yes, a database would also be preferable, but again, we don't have time
to build it properly, and test it to the standards required...

thanks for your help or suggestions if I cannot use INDIRECT...

Philip
 
Hi Phlipp
see my response to Harlan in the other thread using INDIRECT.EXT. Just
test if this does work in combination with MATCH (not sure, could be
that it does not work)
 
Hi,

Yes we have ORACLE and SQL Server databases - and build applications, but
the full and proper development, testing, deployment lifecycle takes 3 months
minimum and we only have 2 weeks...

Basically we're building a tool for a new fund group (for a new client) to
use just to calculate the expenses of the funds - and Excel (either with or
without VBA) .

What happens is, the mainframe (y'know IBM AS 400 type mother of all systems
with 50,000 users getting data in under 2 seconds etc) stores and calculates
all the information needed to value of the fund (pricing etc), then satellite
applications (built in C++ and SQL Server 2000) check everything, highlight
the problems or exceptions for investigation and finally output the fund
Total NET Asset Value, at the class level into the export Excel sheets.

It's these export sheets that I would use as the source for my formulae...I
am building a control sheet that will store the Total NET Asset Value for
each fund (by class), and for each fund there is a separate fund expenses
sheet with all the formulae to calculate the individual expenses for the fund.

So at the moment I am looking to build the middle part, the last part
(individual expense sheets for each fund) is not a problem as that's a
template, and we can just put the TNA in and all the expenses at the fund
level will be calculated in each individual fund sheet by the formulae...

Then I can look into doing something similar for the individual expenses
sheets at the end of the process...

So it goes like this:

Source Data from 100 workbooks > my main workbook > individual expense sheets

So, would INDIRECT.EXT be useful with ISERROR to be able to retrieve the TNA
from all these source sheets at the class level for each fund into my main
sheet?

thanks for any help or assistance...

Philip

Frank Kabel said:
Harlan Grove said:
Nevertheless, it'd be faster than collecting the information manually, which
appears to be what you're implying the OP should do since you don't deign to
offer those alternatives.
Click to expand...

Just forgot to add the link :-)
http://tinyurl.com/2c62u

And for the OP: In your case probably INIRECT.EXT would work:

=IF(ISERROR(INDIRECT.EXT("'" & A1 & "[" & B1 & "]" & C1 &
"'!A1")),"Fund sheet does not
exist",IF(ISNA(MATCH(B1,INDIRECT.EXT("'" & A1 & "[" & B1 & "]" & C1 &
"'!A1:X1"),0)),"Class not
active",INDEX(INDIRECT.EXT("'" & A1 & "[" & B1 & "]" & C1 &
"'!A2:X2"),1,MATCH(B1,INDIRECT("'" & A1 & "[" & B1 & "]" & C1 &
"'!A1:X1")))))

Assumptions:
column A: path (e.g. C:\temp\)
column B: filename (e.g. test.xls)
column C: sheetname (e.g. fund1)
column D: class name


Just test it :-)


And if the OP or the OP's users don't have database software? And if the
data is in nontabular Excel files to begin with?

To the extent consolidated data could be used in *subsequent* calculations
more involved that counting, summing or averaging, it's arguable whether the
OP's task would be better suited to spreadsheet or database.
Click to expand...

In this case I just made some simple assumptions:
- he is talking about funds with several classes (currencies)
- I would expect that each sheet contains either transaction/pricing
data, or static data for specific funds
- we're talking about > 100 funds
- at least from the description I got the 'feeling' that the data on
each sheet is ordered in a database like structure -> but of course
this may be wrong

I know that several (even large banks) are using Excel for managing
funds, etc BUT this is not the right tool for storing this kind of
data. This should be stored in a database like application. and if the
OP does not have a database system he should really consider buying one
:-)

I agree with you that if you want to calculate with the data you may
use Excel for this (and not a database) but still use a central storage
system.

Frank
Click to expand...
 
Hi
just try it. It could be that INDIRECT.EXT does not work as second
parameter of a MATCH function. If not you're probably out of luck.
What would help if you could provide a formula which works for one
workbook/sheet if it is open. Using this as a starting point we could
add INDIRECT(.EXT) calls to it. Otherwise this is difficult as I don't
know your cell ranges, etc.

--
Regards
Frank Kabel
Frankfurt, Germany

Philip said:
Hi,

Yes we have ORACLE and SQL Server databases - and build applications, but
the full and proper development, testing, deployment lifecycle takes 3 months
minimum and we only have 2 weeks...

Basically we're building a tool for a new fund group (for a new client) to
use just to calculate the expenses of the funds - and Excel (either with or
without VBA) .

What happens is, the mainframe (y'know IBM AS 400 type mother of all systems
with 50,000 users getting data in under 2 seconds etc) stores and calculates
all the information needed to value of the fund (pricing etc), then satellite
applications (built in C++ and SQL Server 2000) check everything, highlight
the problems or exceptions for investigation and finally output the fund
Total NET Asset Value, at the class level into the export Excel sheets.

It's these export sheets that I would use as the source for my formulae...I
am building a control sheet that will store the Total NET Asset Value for
each fund (by class), and for each fund there is a separate fund expenses
sheet with all the formulae to calculate the individual expenses for the fund.

So at the moment I am looking to build the middle part, the last part
(individual expense sheets for each fund) is not a problem as that's a
template, and we can just put the TNA in and all the expenses at the fund
level will be calculated in each individual fund sheet by the formulae...

Then I can look into doing something similar for the individual expenses
sheets at the end of the process...

So it goes like this:

Source Data from 100 workbooks > my main workbook > individual expense sheets

So, would INDIRECT.EXT be useful with ISERROR to be able to retrieve the TNA
from all these source sheets at the class level for each fund into my main
sheet?

thanks for any help or assistance...

Philip

Frank Kabel said:
Harlan Grove said:
as said this procdure works only within ONE workbook. INDIRECT won't
work on closed workbooks. there're some alternatives but using
Click to expand...
that
on
100 separate workbooks it will be horrible slow (at least I would
assume that).

Nevertheless, it'd be faster than collecting the information manually, which
appears to be what you're implying the OP should do since you
Click to expand...
don't
deign to
offer those alternatives.
Click to expand...

Just forgot to add the link :-)
http://tinyurl.com/2c62u

And for the OP: In your case probably INIRECT.EXT would work:

=IF(ISERROR(INDIRECT.EXT("'" & A1 & "[" & B1 & "]" & C1 &
"'!A1")),"Fund sheet does not
exist",IF(ISNA(MATCH(B1,INDIRECT.EXT("'" & A1 & "[" & B1 & "]" & C1 &
"'!A1:X1"),0)),"Class not
active",INDEX(INDIRECT.EXT("'" & A1 & "[" & B1 & "]" & C1 &
"'!A2:X2"),1,MATCH(B1,INDIRECT("'" & A1 & "[" & B1 & "]" & C1 &
"'!A1:X1")))))

Assumptions:
column A: path (e.g. C:\temp\)
column B: filename (e.g. test.xls)
column C: sheetname (e.g. fund1)
column D: class name


Just test it :-)


This sound more like a database application and not like an Excel
application!

And if the OP or the OP's users don't have database software? And
Click to expand...
if
the
data is in nontabular Excel files to begin with?

To the extent consolidated data could be used in *subsequent* calculations
more involved that counting, summing or averaging, it's arguable whether the
OP's task would be better suited to spreadsheet or database.
Click to expand...

In this case I just made some simple assumptions:
- he is talking about funds with several classes (currencies)
- I would expect that each sheet contains either transaction/pricing
data, or static data for specific funds
- we're talking about > 100 funds
- at least from the description I got the 'feeling' that the data on
each sheet is ordered in a database like structure -> but of course
this may be wrong

I know that several (even large banks) are using Excel for managing
funds, etc BUT this is not the right tool for storing this kind of
data. This should be stored in a database like application. and if the
OP does not have a database system he should really consider buying one
:-)

I agree with you that if you want to calculate with the data you may
use Excel for this (and not a database) but still use a central storage
system.

Frank
Click to expand...
Click to expand...
 
Philip,

I'm not sure if you got your answer.

Have you thought about setting up your main spreadsheet in a way tha
you have 100 separate tabs, each linked to the source data. Fo
instance, you will have a main tab which gathers all of the data, an
you create an individual tab for each fund using >Data>Import Externa
Data>New Database Query, choose Excel file type. When it asks if yo
want to put it on the existing worksheet, or create a new one, creat
the new one and rename the newly created tab with the name of the fund


After the data has returned, you can specify to update those links o
opening the main workbook in the data range properties of the importe
data. Then you can hide those tabs (>Format>Sheet>Hide). Protect th
workbook if you would like, so the tabs cannot be shown.

Use an hlookup function in Excel to get the correct column of data fro
the corect fund and return whichever row you want to on the main tab.
This is an easy way to do what you want to do. Set might be tim
consuming, but you bypass VBA
 
Back
Top