Dynamic table array using Hyperlink
 

Hello,

I have a database of 175 workbooks in different subfolder, I have to make a
single sheet (summary sheet) for main figures in every workbook.

For this I have to write formula for every workbook seperately to call the
value.

I am looking for a way so the workbook path or hyperlink to every individual
workbook can be use as table array, what I am trying is as follows

In Cell E2 : 'S:\Automobile Assembler\Atlas Honda\Internal Research\[JUNE
Time Series Data ATLH.xls]Time Series'!$A$1:$FF$220
In Cell F2: 'S:\Automobile Assembler\Atlas Honda\Internal Research\[JUNE
Time Series Data ATLH.xls]Time Series'!$A$1:$A$220

In Cell B2: BVPS Basic (Column Heading)

In Cell C2: where value is required, formula is

=INDEX(HYPERLINK(E2),MATCH(B2,HYPERLINK(F2),0),14)

The Formula return #Value! error, whether entered as array or simple

Help needed please.

Dynamic table array using Hyperlink
 

Hi

HYPERLINK formula doesn't return values or cell references, it creates a
hyperlink, i.e. when you click on cell with hyperlink, according file or URL
is opened in new window.

To return text string as a range reference, you have to use INDIRECT
function. But there is a drawback - the source file the returned range
refers to MUST be opened at same time. I.e. you have to open all 175
workbooks to get the formulas work.

There were given links in Excel NG's for a couple of UDF's, which have a
similar functionality, but work with closed workbooks.

Another way (in case creating links is an one-time operation) is to create a
text string representing link formula, like
="=" & E2
, and copy it down for whole table.
Then copy the range with those formulas, and convert them to values (use
Paste Special).
At last select same range, and replace all equal signs with equal signs (Use
Replace All feature).


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Salman" wrote in message
...
Hello,

I have a database of 175 workbooks in different subfolder, I have to make
a
single sheet (summary sheet) for main figures in every workbook.

For this I have to write formula for every workbook seperately to call the
value.

I am looking for a way so the workbook path or hyperlink to every
individual
workbook can be use as table array, what I am trying is as follows

In Cell E2 : 'S:\Automobile Assembler\Atlas Honda\Internal Research\[JUNE
Time Series Data ATLH.xls]Time Series'!$A$1:$FF$220
In Cell F2: 'S:\Automobile Assembler\Atlas Honda\Internal Research\[JUNE
Time Series Data ATLH.xls]Time Series'!$A$1:$A$220

In Cell B2: BVPS Basic (Column Heading)

In Cell C2: where value is required, formula is

=INDEX(HYPERLINK(E2),MATCH(B2,HYPERLINK(F2),0),14)

The Formula return #Value! error, whether entered as array or simple

Help needed please.

Dynamic table array using Hyperlink
 

Hi,
I already have the list of the path of every workbook, what i get is that in
last lines you have suggested to combine text to develop the whole path list.
If my understanding is wrong then please elaborate a little more.

in every 175 workbooks there a multiple sheets, sheet name are identical,
and i have to do the same with other sheets, and in other databases as well,
for that i need a way through which i can do this more effeciently, if there
is any suggestion then it would be a great help

Regards

"Arvi Laanemets" wrote:

Hi

HYPERLINK formula doesn't return values or cell references, it creates a
hyperlink, i.e. when you click on cell with hyperlink, according file or URL
is opened in new window.

To return text string as a range reference, you have to use INDIRECT
function. But there is a drawback - the source file the returned range
refers to MUST be opened at same time. I.e. you have to open all 175
workbooks to get the formulas work.

There were given links in Excel NG's for a couple of UDF's, which have a
similar functionality, but work with closed workbooks.

Another way (in case creating links is an one-time operation) is to create a
text string representing link formula, like
="=" & E2
, and copy it down for whole table.
Then copy the range with those formulas, and convert them to values (use
Paste Special).
At last select same range, and replace all equal signs with equal signs (Use
Replace All feature).


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Salman" wrote in message
...
Hello,

I have a database of 175 workbooks in different subfolder, I have to make
a
single sheet (summary sheet) for main figures in every workbook.

For this I have to write formula for every workbook seperately to call the
value.

I am looking for a way so the workbook path or hyperlink to every
individual
workbook can be use as table array, what I am trying is as follows

In Cell E2 : 'S:\Automobile Assembler\Atlas Honda\Internal Research\[JUNE
Time Series Data ATLH.xls]Time Series'!$A$1:$FF$220
In Cell F2: 'S:\Automobile Assembler\Atlas Honda\Internal Research\[JUNE
Time Series Data ATLH.xls]Time Series'!$A$1:$A$220

In Cell B2: BVPS Basic (Column Heading)

In Cell C2: where value is required, formula is

=INDEX(HYPERLINK(E2),MATCH(B2,HYPERLINK(F2),0),14)

The Formula return #Value! error, whether entered as array or simple

Help needed please.

Dynamic table array using Hyperlink
 

Hi


"Salman" wrote in message
...
Hi,
I already have the list of the path of every workbook, what i get is that
in
last lines you have suggested to combine text to develop the whole path
list.
If my understanding is wrong then please elaborate a little more.

Yes. But you have p.e. in cell E2 the text string
"'S:\Automobile Assembler\Atlas Honda\Internal Research\[JUNE Time Series
Data ATLH.xls]Time Series'!$A$1:$FF$220"
You have to generate some formula from this, like
=SUM('S:\Automobile Assembler\Atlas Honda\Internal Research\[JUNE Time
Series Data ATLH.xls]Time Series'!$A$1:$FF$220)

To do this for one single link:
1. Into some cell, p.e. C2, you enter the formula like
="=SUM(" & E2 & ")"
, and you get in C2 displayed
"=SUM('S:\Automobile Assembler\Atlas Honda\Internal Research\[JUNE Time
Series Data ATLH.xls]Time Series'!$A$1:$FF$220)"
NB! You don't get the link so long, and not even the string image of link -
it's only displayed so long.

2. Copy the cell C2, and overwrite the formula with its value (i.e. string),
using Paste Special Values. In C2 you get now
"=SUM('S:\Automobile Assembler\Atlas Honda\Internal Research\[JUNE Time
Series Data ATLH.xls]Time Series'!$A$1:$FF$220)"

3. With cell C2 selected, activate replace, enter equal sign into both
search and replace fields, and click Replace button. The string is converted
to formula
=SUM('S:\Automobile Assembler\Atlas Honda\Internal Research\[JUNE Time
Series Data ATLH.xls]Time Series'!$A$1:$FF$220)
, and according sum is displayed.

To do the same for all workbooks and sheets at one go, you have to design
the formula at step 1 in such a way, that by copiyng it you get all
displayed formula strings riht. And in steps 2-3 you work with range of
cells instead of single cell.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

Dynamic table array using Hyperlink
 

Hello,
Thanks alot, I try this on a single case and it works, now i am going to
apply it on the whole, thanks for your help.

"Arvi Laanemets" wrote:

Hi


"Salman" wrote in message
...
Hi,
I already have the list of the path of every workbook, what i get is that
in
last lines you have suggested to combine text to develop the whole path
list.
If my understanding is wrong then please elaborate a little more.

Yes. But you have p.e. in cell E2 the text string
"'S:\Automobile Assembler\Atlas Honda\Internal Research\[JUNE Time Series
Data ATLH.xls]Time Series'!$A$1:$FF$220"
You have to generate some formula from this, like
=SUM('S:\Automobile Assembler\Atlas Honda\Internal Research\[JUNE Time
Series Data ATLH.xls]Time Series'!$A$1:$FF$220)

To do this for one single link:
1. Into some cell, p.e. C2, you enter the formula like
="=SUM(" & E2 & ")"
, and you get in C2 displayed
"=SUM('S:\Automobile Assembler\Atlas Honda\Internal Research\[JUNE Time
Series Data ATLH.xls]Time Series'!$A$1:$FF$220)"
NB! You don't get the link so long, and not even the string image of link -
it's only displayed so long.

2. Copy the cell C2, and overwrite the formula with its value (i.e. string),
using Paste Special Values. In C2 you get now
"=SUM('S:\Automobile Assembler\Atlas Honda\Internal Research\[JUNE Time
Series Data ATLH.xls]Time Series'!$A$1:$FF$220)"

3. With cell C2 selected, activate replace, enter equal sign into both
search and replace fields, and click Replace button. The string is converted
to formula
=SUM('S:\Automobile Assembler\Atlas Honda\Internal Research\[JUNE Time
Series Data ATLH.xls]Time Series'!$A$1:$FF$220)
, and according sum is displayed.

To do the same for all workbooks and sheets at one go, you have to design
the formula at step 1 in such a way, that by copiyng it you get all
displayed formula strings riht. And in steps 2-3 you work with range of
cells instead of single cell.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )