Automatic Update on Excel

The_Hurricane

Hi All,

I have a spreadsheet (spreadsheet_2) that I want cell A2 to be automatically updated by what is in cell D4 of spreadsheet_1.

At the moment I have used the following simple formula, however how would I go about using a VLOOP instead for this function?

='[spreadsheet_2.xlsx]sheet1!$D$4

Lum

Anywhere in an excel formula that allows you to reference a cell can also reference a different spreadsheet by using the exact syntax you used there.

e.g

=VLOOKUP(A1,'D:\Documents\Lum\[Book1.xlsm]sheet1'!D5,3)

The_Hurricane

Hi,

I tried the following formula but it's bringing up an error message.

I've tweaked the names around so that the source spreadheet is called "August 2012" and the tab "Finances". It's supposed to find the current value in cell A6 which is "January" match the word January from column A of the August 2012 spreadsheet and return the corresponding value in column J.

=VLOOKUP($A$6,'[August 2013.xlsx]Finances'!$A:$J,10,FALSE)

Lum

should be =VLOOKUP not =VLOOPUP

paddyrg

Be cautious about linking data between files - as soon as you move/email/anything one of those sheets you'll start getting warnings about not being able to update. Try to keep everything within the same file for an easier life.

The_Hurricane

Lum wrote: »

should be =VLOOKUP not =VLOOPUP

Thanks, that was the problem.

The_Hurricane

paddyrg wrote: »

Be cautious about linking data between files - as soon as you move/email/anything one of those sheets you'll start getting warnings about not being able to update. Try to keep everything within the same file for an easier life.

Valid points, however some of the spreadsheets have to be kept separate as they are too large to put onto the one worksheet.

The_Hurricane

Just following on from this formula, is there anyway to get the second occurence of A6 in the August spreadsheet returned instead of the first?


=VLOOKUP($A$6,'[August 2013.xlsx]Finances'!$A:$J,10,FALSE)

Lum

Yes, but it's more work.

http://chandoo.org/wp/2010/11/10/vlookup-second-value/