INDIRECT reference to named cell in closed workbook

Paulfakins

New Member
Joined
Oct 9, 2003
Messages
29
I have the following as the source for a data validation list:

=INDIRECT(CONCATENATE("[Workbook1.xls]Sheet1!",A1))

where A1 will contain the name of the range I want to use as the list. This works fine as long as Workbook1 is open.

Is there a way to do this if Workbook1 is closed? I can't seem to get it to work even if I use the full path for the workbook.

e.g. =INDIRECT(CONCATENATE("c:Documents\data\[PipePrice.xls]Pipe!",C12))

Any advice would be appreciated

Paul :rolleyes:
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
the search feature of the board is remarkably effective :)

I'd suggest searching for something like "indirect closed".

(& while I'm here, the morefunc addin has an indirect.ext() function that works for closed workbooks).
 

Paulfakins

New Member
Joined
Oct 9, 2003
Messages
29
Looks like the morefunc add-in is probably the way to go.

Thanks to both of you. In the future I guess I should spend a little time looking for similiar posts before starting a new thread.

Paul
 

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
Not always... it's amazing how some people can discover some new element that no-one else saw.

But, sometimes people love re-inventing the wheel :p

I guess thats what Undergrads are there for :eek:


(y)
 

Paulfakins

New Member
Joined
Oct 9, 2003
Messages
29
Paulfakins said:
I have the following as the source for a data validation list:

=INDIRECT(CONCATENATE("[Workbook1.xls]Sheet1!",A1))

where A1 will contain the name of the range I want to use as the list. This works fine as long as Workbook1 is open.

O.K. Here we go:

I followed the advice I was given and loaded the Morefunc Add-In. I have used the INDIRECT command successfully to access information in a closed workbook, However;

When I try to use it in Data Validation, I cannot get it to work.

Here is the syntax I am using in the data Validation Source:

=INDIRECT(CONCATENATE("'C:\temp\[book1.xls]Sheet1'!",,A1))

When I enter this information I get a message box that reads:

The Source currently evaluates to an error. Do you wish to proceed?

Am I entering the formula incorrectly?



Background:

Book1 is a Price Book. It contains about 50 named ranges which consist of various categories of parts. Through a series of Data Validation Lists, the user steps through category selections and the final list (the one that uses the range from Book1) shows only the parts in the sub-category they have selected.

I have had all of this working quite well as long as the Price Book(Book1) is open. I only hit this snag when I tried making this work with the workbook closed.


Any advice would be appreciated. I would even be willing to consider moving the price data to Access, but I am not familiar with how to retrieve data from Access with XL.

Thank you,

Paul
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,088
Messages
5,768,034
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top