Updating spreadsheets from another spreadsheet

[Mitch]

I need to update spreadsheet 2 from spreadsheet 1.

Within spreadsheet 1 I have a column of job numbers in
column A ( 1,2,3,4,5........ ) , in columns b,c,d & e of
this spreadsheet I have other information relevant to this
job number.

What I want to do is type in a job number in spreadsheet 2
with a job number from this list ( say 4 ) and for various
fields in spreadsheet 2 to self populate from spreadsheet
1.


Mitch

 

[JulieD]

Hi Mitch

use the VLOOKUP function for this
with your table in sheet1 range A2:E6
and in A1 of spreadsheet two you're typing in the job number
then the vlookup for B1 would be
=VLOOKUP(A1,Sheet1!$A$2:$E$6,2,0)
which says lookup the value in A1, in the table in sheet1 and return the
information from the 2nd column where there is an exact match.
you can then adjust the formula for the other data you want

BTW if you use this formula in B1 and there's no data in A1 you'll get a #NA
error this can be supressed by changing the formula to
=IF(ISNA(VLOOKUP(A1,Sheet1!$A$2:$E$6,2,0)),"",VLOOKUP(A1,Sheet1!$A$2:$E$6,2,0))

hope this helps
Cheers
JulieD