Contact list and look up table

dave.mcpherson56751

Hi,

I am trying to set up automated alert to an area manager that is associated with certain stores. (Several Stores and several managers) This information is driven by forms and the only information that is chosen ( for this part) is the store name.

I have managed to create tables and formulas that then auto populate the Area Managers name.

However i cannot populate the information into a contact list column. this in turn is not allowing me to automate alerts as i believe you can only do this via a contact column

Is there any thing i can do?

Any help would be greatfully received.

Thank you for reading

Dave

Andrée Starå

Hi Dave,

How many Area Managers is involved?

I hope this helps you!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

dave.mcpherson56751

Hi Andree,

there are 40 Area Managers across 200 stores

thank you

 

Dave

Andrée Starå

One workaround could be to set up Alerts & Actions for the different Area Managers that are triggered by the Area Manager column in the sheet.

Would that work?

Paul Newcome

Unfortunately you can't use formulas in a contact column. The only thing I have found that works for this purpose is to set up a rule for each individual that says if a row is added or changed where the name in the Owner column is John Doe. Then clone it and change the name to Jane Doe. Clone and change it to John Smith, so on and so forth.

dave.mcpherson56751

Hi All,

 

Thank you for your feedback and ideas. i think i have found a work around and that is by making the site address the contact column. so when the user chooses the site, it displays the site name but is actually a contact column with the correct AM's e-mail address sat behind it.

 

Andrée Starå

Wow!

That's a great workaround. Now, why didn't I think of that? 

Paul Newcome

How long did this take to set up? I would imagine you had to go into your SS contacts and create each of these for each site?

dave.mcpherson56751

Hi Paul,

 

A lot of copy and pasting but set all the contacts up as new, if any change i will be furious!!

Thanks everyone for their ideas and input.

Dave

Andrée Starå

Happy to help!

Paul Newcome

That's what I was afraid of. I was hoping you found something easy and flexible.

 

I use a checkbox column for each contact with the column header as the contact's name that's used in the drop down or table (whichever is used to populate the name field) (most I've had to use this way is 17, and even that was a bit of a headache the first time around). In row one I put each person's name in the appropriate column (matches column header). In row two I put, =IF([Name Column]@row = [John Smith]$1, 1) From there I can drag fill down and across. Locking in the row 1 reference ensures that part won't auto-update as you drag fill down. Leaving the column name reference open and having row 1 match the column header allows you to reference each new name as you drag fill across.

 

I then build a notification that says "If the box in column John Smith is checked, send a notification to John Smith".

 

I then clone the rule and select the next person's name (which is listed out as they are separate columns and I used their name as the column header) going on down the line until I've covered everyone.

 

Another tedious work around, but once you've done it a time or two you can move through it relatively quickly.

 

I do like your idea for larger scale though as mine requires both a new column AND notification for each person, but as you said... Any changes are frustrating.