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.