Update values and Vlookup

[Moll45]

I have two spreadsheets one is 2 columns x 200 rows the other master sheet is 250 columns x 500 rows, the master spreadsheet performs vlookup on the smaller one

My problem is that the master sheet will not fully update (some cells do) unless I press F2 on each cell this then brings up a box as though you were going to open a file although it says Update values: in the top right of the box. I then have to select the file I'm trying to update from (there are a quite a few!) - am I asking too much of the file or (more than likely) what have I done wrong?

Hoping someone can help

Thanks,

Moll

 

[XL Guru]

Not sure where to start, Moll.

Does it make any diff if you have the source file open or not when you open the master?

In the master, check Edit -- Links ; "Update" = Automatic?

 

[Yorkshire Guy]

Hi Moll,

Not sure what is happening here.

Have you by any chance moved the 'smaller spreadsheet' since you put in the links from the 'master'?

F2 is for EDIT.

Is the box that opens the LINKS selection box, ie. is it asking you to select where is the 'smaller spreadsheet'?

Looking through those cells that do update and those that don't , is the path to the 'smaller spreadsheet' any different.

In your MASTER try EDIT / LINKS and set to update automatic.
Also, have you tried copying the cells that do update through those that don't i.e to make them all the same?

lol
Hew

 

[Moll45]

Thanks for your replies guys,

Hi Andy - (hope you are well - long time no help ;o) ),

No it doesn't make any difference at all and I have the Update links set to auto.



Hi Hew,

Have you by any chance moved the 'smaller spreadsheet' since you put in the links from the 'master'?

No nothing has changed

Is the box that opens the LINKS selection box, ie. is it asking you to select where is the 'smaller spreadsheet'?

Yes - but it's where I put it originally

Looking through those cells that do update and those that don't , is the path to the 'smaller spreadsheet' any different.

No - not at all

In your MASTER try EDIT / LINKS and set to update automatic.
Also, have you tried copying the cells that do update through those that don't i.e to make them all the same?

I've tried copying the links and everything is set to automatic (I've actually tried both ways)

Is there a limit to how many files you can link to one master spreadsheet?

Thanks again,

Moll

 

[Yorkshire Guy]

Hi Moll,

and Andy,

Looks like you were responding at the same time, dare I continue!

I've had funny stuff before with linked files and as Andy says, it does depend on whether the Linked s/s's are already open.

There is another flag that affects depending on the latter, Tools / Options / Edit / Ask to Update Links - to enforce this I have previously had to unflag, save & close the s/s, then re-open and change back so as not to ask.

I cannot qualify on a limit to the number of linked files (how many do you have?) but there is / was an issue with the length of the path (i.e. no of characters) in the formula.

Have you tried Updating the Source links to force the path, without the other files open and with?

Sorry, this is wandering!

lol
Hew

Silly Q, you do have auto calc switched on?, not manual.

 

[Moll45]

Hi Hew,


There is another flag that affects depending on the latter, Tools / Options / Edit / Ask to Update Links - to enforce this I have previously had to unflag, save & close the s/s, then re-open and change back so as not to ask.

I'll try this

I cannot qualify on a limit to the number of linked files (how many do you have?) but there is / was an issue with the length of the path (i.e. no of characters) in the formula.

I have 250 smaller files feeding into the master one. The length of the path is fine.

Have you tried Updating the Source links to force the path, without the other files open and with?

Yes

Silly Q, you do have auto calc switched on?, not manual.

Definitely not a silly question where I'm concerned




Thanks

Moll

 

[XL Guru]

"auto calc switched on" is via Tools -- Options -- Calculation.