Updating lookups from a closed spreadsheet

Hello All,

I feel like I could figure this out but would rather save myself time.

I created templates for our sales people to use to enter their forecasts into. They supply 3 fields and then there are about 10 fields that based on the part number lookup in a separately maintained master reference file by part. I'd like for them to be able to see as they plug in there parts that the lookups pull the correct info or if N/A they know they need to go to the master to fill in that new part.

Curious if there is a way to do this without have everyone have to open the spreadsheet for the lookups to work for them.

Thanks,

CMO

What formula are you using for the lookups? INDEX, MATCH, VLOOKUP will work on a closed file.

Originally Posted by cmoexcel

Using vlookup but can use anything. The lookup is referencing a separate, closed workbook though. I want the file to update without needing the closed file open.

Let me try again. What formula are you using for the lookups? VLOOKUP works on a closed file.

I must not be explaining something correctly.

My fields in column A,B,C are handoff time, part number and quantity. Based on part number, columns E-L vlookup into a mapping table which is in it's own workbook and closed.

If I enter a new row of a data into the table and don't have the mapping table file open, all columns E-L say are "#REF!". If I go open the closed mapping file, they then map and I'm all good.

But I'm trying to avoid having 10 people open that file.

I have requested to see the formula twice. I can't help any further without seeing something concrete.

Perhaps you have an INDIRECT function within your VLOOKUP formula (which you haven't quoted to us, by the way), and if so this will NOT work with closed workbooks.

Without seeing a sample workbook, or just the formula that you are having problems with, then it is all just a guessing game.

Hope this helps.

Pete

Sorry everyone - newbie here. Attached a sample of the workbook.

Here's the formula:

=IFNA(VLOOKUP([@[Part Number]],'\\VALUEADDCO.COM\dfs\DPW\Departmental\Sales\AUTOMOTIVE\Part Mapping\Part Mapping Master.xlsx'!CurrentParts_Table[#Data],7,FALSE),VLOOKUP([@[Part Number]],'\\VALUEADDCO.COM\dfs\DPW\Departmental\Sales\AUTOMOTIVE\Part Mapping\Part Mapping Master.xlsx'!CurrentParts_Table[[SECONDARY PART NUMBER]:[Angies Report]],6,FALSE))

Now at first glance it seems to me that should work, but I can't do much with it since I don't have the Part Mapping Master.xlsx file. Two possibilities occur to me:

The referenced file appears to be on another server, and external references might not work over a network. I have not tried it so I can't say for sure.
The references are to tables. A VLOOKUP to a table in a closed file failed in my test, although using a range reference to the same data worked.

So apparently there are some limitations to the ability of VLOOKUP to work on a closed file.

The first thing I would try is to change the table references to range references. I would have provided that formula but cannot do that without your master file. If that does not work, the the only other option I can think of is VBA that opens the file for the user automatically or uses ADO to retrieve data from a closed file.

Hi 6 String,

Attached is dummy copy of the mapping for that part with all sensitive data removed.

The files are both on the same network, so I don't think that is the issue.

What is ADO?

ADO is a protocol that treats a closed Excel file as if it were a database. ADO calls can read data from a closed Excel workbook, although IIRC not Excel formatting or other details.

First I would suggest rewriting your formula to use range references instead of table references. That is the easiest thing to try first and might work.