INDIRECT update with external workbook

**papa_face** · 05-02-2008, 02:44 PM

Hello,

Is it possible for the INDIRECT function to be tweaked so it updates even when the external workbook is closed?

Also does anyone have any idea why

Please Login or Register  to view this content.

Produces a #REF! error code?

Any help would be greatly appreciated :D

**VBA Noob** · 05-02-2008, 03:17 PM

See if links help

http://www.dailydoseofexcel.com/arch...sed-workbooks/
http://www.j-walk.com/ss/excel/tips/tip82.htm
http://www.contextures.com/xlFunctions05.html#RefWkbk

VBA Noob

**NBVC** · 05-02-2008, 03:20 PM

You could download and install a free Morefunc.dll addin from here: http://xcell05.free.fr/english/ and use the Indirect.ext function

=INDIRECT.EXT("C:\Documents and Settings\*****\Desktop\Excel[book2.xls]Sheet1!C"&C2)

**papa_face** · 05-02-2008, 03:22 PM

I've used the INDIRECT.EXT function and it works brilliantly. But ideally, I need to get it to work without installing add-in's into excel

Is there any other way to do this?

Also any feedback on the reason why my INDIRECT ^^^ function isn't returning a result with the filename?

Thanks!

**NBVC** · 05-02-2008, 03:27 PM

Originally Posted by papa_face

...

Also any feedback on the reason why my INDIRECT ^^^ function isn't returning a result with the filename?

Thanks!

should there be another \ after EXCEL?

**papa_face** · 05-02-2008, 03:37 PM

I tried:

Please Login or Register  to view this content.

It gave me:

Please Login or Register  to view this content.

But

Please Login or Register  to view this content.

works.
I need it to use a full path to gain access to the file.

But having said that, INDIRECT is only meant to use open workbooks so it would never need the FULL path, just the sheet name.

Is there any other way of referencing a cell dynamically, like I have done in the above code, but so it will update automatically without the workbook open?

**NBVC** · 05-02-2008, 03:40 PM

How about:

Please Login or Register  to view this content.

**papa_face** · 05-02-2008, 03:46 PM

Originally Posted by NBVC

How about:

Please Login or Register  to view this content.

Still produces #REF!

What I am trying to do is this:

Please Login or Register  to view this content.

But as you can see that isn't dynamic.

Strictly speaking I would like it to be:

Please Login or Register  to view this content.

But excel won't allow me to do that, which is why I needed to use INDIRECT.

This is a screenshot of what I am trying to do:
http://img514.imageshack.us/img514/8...anationcx1.jpg

As you can see I can easily generate the row number I am looking for. Row 16 for speakers on book2 for example.

I need a way of finding the value using the row number dynamically.

Any suggestions?

**NBVC** · 05-02-2008, 03:52 PM

Well if the workbook is open and the path is correct, including the spelling of all folders, sheets, etc... then I can't see why it doesn't work.. the syntax looks fine to me...

**papa_face** · 05-02-2008, 03:54 PM

I don't think INDIRECT is designed to use full path names as there is no need for it to, as its intended purpose is to ONLY gather data from open workbooks.

Do you have any suggestions of a way around to do what I need?

Basically I just need a way of finding the value of data in a given cell (is calculated in my sheet already) and column.

**NBVC** · 05-02-2008, 04:02 PM

oddly enough, Indirect will work with the whole path... it's just that the workbook has to be opened.....

anyways, maybe you can use Vlookup?

Something like:

Please Login or Register  to view this content.

This will look up the item from B2 in column A of the other workbook and pull the data from column C in the same row...

Vlookup does work on closed workbooks

copied down

**papa_face** · 05-02-2008, 04:16 PM

That works pretty well

Is there any way to adapt that so it can tell it to look in a particular cell?

As with

Please Login or Register  to view this content.

It would not allow me to put in new columns which is probably going to happen...

I ideally need a way to telling the function to look at a cell to get the row number and insert it into the function.
Like:
C2 = 3

Function:

Please Login or Register  to view this content.

is interpretted as:

Please Login or Register  to view this content.

Which is what indirect does, but obviously that doesnt work the way I want it to.

Thanks for your help so far by the way :D

**NBVC** · 05-02-2008, 08:49 PM

The only other thing I can suggest is the Index() function...

e.g. =INDEX([book2.xls]Sheet1!A:D,C2,2)

syntax: INDEX(array,row_num,[col_num])

This function looks at the table range [book2.xls]Sheet1!A:D and extracts the item in the row identified by cell C2 and from column 2.

So if you have a 3 in cell C2, it will extract the item in B3 of [book2.xls]Sheet1.

**papa_face** · 05-03-2008, 06:24 AM

Thanks :D
Thats more practical than the VLOOKUP function for the intended use, as if the row position changed I would have to edit every single formula. But I can easily reference a column number in a cell and make it change them all :D

Thanks for your help!

INDIRECT update with external workbook

LinkBack

Thread Tools

Rate This Thread

Display

INDIRECT update with external workbook

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions