Efficient linking

[teh_chucksta]

I have a master summary workbook that will feed from 20 other workbooks
throughout my company's directory. Within each of the aforementioned
workbooks, I will link across hundreds of cells. That's a lot of links.
Before I begin I would appreciate any feedback redarding methodology / most
efficient ways to accomplish this. Not looking for VB solutions.

Thanks in advance,
Charlie

 

[Pete_UK]

Depends what your summary sheet is meant to do, but the simplest
linking is along these lines:

=IF(Sheet2!A1="","",Sheet2!A1)

This will bring the individual cell A1 from Sheet2, and the formula
can be copied across and down to bring other cells across.

Of course, if Sheet2 is not in the same workbook then you will need to
amend the formula along these lines:

=IF('full_path[filename.xls]Sheet2'!
A1="","",'full_path[filename.xls]Sheet2'!A1)

although if the file is open at the same time then you don't need to
have the full_path (Excel will put it in if the file is closed).

However, there may be other things you want to do in the summary
sheet, and so you might have INDEX/MATCH or VLOOKUP formulae, or SUMIF
or SUMPRODUCT, depending on how you want to summarise the data.

Hope this helps.

Pete

 

[teh_chucksta]

My suumary workbook links will be exclusively of the
'full_path[filename.xls]Sheet2'!A1' variety as this is the only way I know to
do it. Just wondering if there are more powerful ways of accomplishing the
same thing, that will help me avoid the wait of updating all the links
against closed files in other directories.

There's an indirect function available in an add-in that works on closed
files but I don't want to have to download it to 15+ users computers.

Thanks for the quick response Pete and let me know if you have other ideas.

Depends what your summary sheet is meant to do, but the simplest
linking is along these lines:

=IF(Sheet2!A1="","",Sheet2!A1)

This will bring the individual cell A1 from Sheet2, and the formula
can be copied across and down to bring other cells across.

Of course, if Sheet2 is not in the same workbook then you will need to
amend the formula along these lines:

=IF('full_path[filename.xls]Sheet2'!
A1="","",'full_path[filename.xls]Sheet2'!A1)

although if the file is open at the same time then you don't need to
have the full_path (Excel will put it in if the file is closed).

However, there may be other things you want to do in the summary
sheet, and so you might have INDEX/MATCH or VLOOKUP formulae, or SUMIF
or SUMPRODUCT, depending on how you want to summarise the data.

Hope this helps.

Pete

I have a master summary workbook that will feed from 20 other workbooks
throughout my company's directory. Within each of the aforementioned
workbooks, I will link across hundreds of cells. That's a lot of links.
Before I begin I would appreciate any feedback redarding methodology / most
efficient ways to accomplish this. Not looking for VB solutions.

Thanks in advance,
Charlie

 

[Pete_UK]

Well it is a lot quicker to link to a worksheet in the same workbook,
so you could think about copying those other worksheets into your
summary file (the originals remain unchanged) then you don't have to
worry about files being open at the same time. You could think about
doing this copying automatically via a macro when you open the summary
file, so you will then just have a short delay when the summary file
is opened. If the source files are changed frequently, you could have
a button on your summary to Update (i.e. bring across all the copies)
and do this on demand.

Hope this helps.

Pete

My suumary workbook links will be exclusively of the
'full_path[filename.xls]Sheet2'!A1' variety as this is the only way I knowto
do it. Just wondering if there are more powerful ways of accomplishing the
same thing, that will help me avoid the wait of updating all the links
against closed files in other directories.

There's an indirect function available in an add-in that works on closed
files but I don't want to have to download it to 15+ users computers.

Thanks for the quick response Pete and let me know if you have other ideas..

Depends what your summary sheet is meant to do, but the simplest
linking is along these lines:

This will bring the individual cell A1 from Sheet2, and the formula
can be copied across and down to bring other cells across.

Of course, if Sheet2 is not in the same workbook then you will need to
amend the formula along these lines:

=IF('full_path[filename.xls]Sheet2'!
A1="","",'full_path[filename.xls]Sheet2'!A1)

although if the file is open at the same time then you don't need to
have the full_path (Excel will put it in if the file is closed).

However, there may be other things you want to do in the summary
sheet, and so you might have INDEX/MATCH or VLOOKUP formulae, or SUMIF
or SUMPRODUCT, depending on how you want to summarise the data.

Hope this helps.

- Show quoted text -