Trying to automate data to pull from 1 sheet too another when location is filled

slrank

Hoping someone can help me, i'm trying to get office locations to populate from 1 sheet to the data sheet.

Location Sheet i have the columns set up as

Office names, Address, City, Province, Postal Code,Country

I want them to populate in the data sheet where if someone puts Office name: Toronto, it will auto populate into the following columns, address, city, province, postal code, country.

Every formula i keep trying or automation doesn't seem to be working.

kowal

hello @slrank

all you need is vlookup formula (or combination of index / match formulas) you can watch it here:

https://www.youtube.com/watch?v=_vMuSLpOZ_g

so for vlookup it works this way:

in column Business Street, Business Province etc you use formulas (and than change entire column into formula)

=vlookup (search_value, lookup_table, column_num, 0)

a) in your case: as search_value you write: [city name]@row

b) for lookup value you put range from another sheet and you choose Location Data sheet and mark all the 6 columns.

c) in column Business Street for column_num you write 2, for business city 3, for province 4 etc.

d) you always use 0

You can read more about vlookup in the smartsheet functions.

hope it helps.