Formula for auto populate?

cxfl2020

In the REP field, every rep is assigned to an area. Would there be a formula that if I choose SALES REP 1 it will auto-populate on the state field AREA 3? Thanks

parulmishra

@cxfl2020 please elaborate you want to choose Sales REP 1 on a second sheet and have Area 3 automatically populated on the second sheet

Paul Newcome

It looks like you have "Rep 1" listed for two different areas. Would you want both of those areas pulled in if you enter "Rep 1" on the other sheet, or would you want it to only pull one of them. If you only want it to pull one of them, how would you determine which one to pull?

cxfl2020

I have a list of reps and all reps are assigned to an area. Now what I want to happen is if I type the name of the sales rep (Column A) it will auto-populate the area (Column B). My goal is not to type their area.

cxfl2020

to be clear there are 48 sales rep and 7 areas.

parulmishra

Hi @cxfl2020 Taking this as reference

You can try something like this

=JOIN(COLLECT({State}, {REP}, [Primary column]@row, 0), " ")

As soon as you type SalesRep1 in your Primary Column it will fetch Area 1 and Area 2 value in your column 2

Paul Newcome

https://community.smartsheet.com/discussion/comment/378913#Comment_378913

Right. That's why I asked how you wanted it to work if you have the same sales rep listed for multiple areas such as how you have it in your screenshot. If you want to pull all areas in, then you will need a JOIN/COLLECT, but if you wanted to only pull one area in then we would need to establish some logic to determine which one.