pull data from multiple sheets with formula referencing a tab name

[liketotrampoline]

Hi - I can't figure this out and hoping someone can help. I have 25 sheets &
I need data from a row from each sheet.

The 25 files have file names which equal cell A8 plus audit.xls = 6L
audit.xls (All names are in column A)
The tab names are dates in this format m-d-yy in from H5

I am trying to pull the value in K10

This formula is not working:

="='["&(A8)&" audit.xls]"&H5&"'!K$10"

What am I doing wrong?

 

[Joel]

from
="='["&(A8)&" audit.xls]"&H5&"'!K$10"

to
=INDIRECT("'["&(A8)&" book1.xls]"&H5&"'!"&K$10)

If this doesn't work use the Evaluate formula tool

Clcik on Cell, Tools - Formula Auditing - evaluate Formula

 

[Dave Peterson]

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

Hi - I can't figure this out and hoping someone can help. I have 25 sheets &
I need data from a row from each sheet.

The 25 files have file names which equal cell A8 plus audit.xls = 6L
audit.xls (All names are in column A)
The tab names are dates in this format m-d-yy in from H5

I am trying to pull the value in K10

This formula is not working:

="='["&(A8)&" audit.xls]"&H5&"'!K$10"

What am I doing wrong?

 

[Jaazaniah]

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

Quick note, working on a project requiring this to work, but when I
try your link I get a french 403 page (forbidden) any help on an
alternate source? is the site down?

-Jaazaniah Cole

 

[Dave Peterson]

It seems to have been down for a couple of days.

A search of google came up with this:
http://www.download.com/Morefunc/3000-2077_4-10423159.html

I don't know if it's current, but it's pretty new. The setup.exe inside the zip
file is dated October 28, 2007.

 

[Jaazaniah]

It seems to have been down for a couple of days.

A search of google came up with this:http://www.download.com/Morefunc/3000-2077_4-10423159.html

I don't know if it's current, but it's pretty new.  The setup.exe inside the zip
file is dated October 28, 2007.

Thanks Dave. Working great so far.