Is this an IF THEN helper sheet or just adding a new column?

damon.tackett

I am trying to pull information to a smartsheet ("working") based on matching two columns on a separate "helper" sheet

so for example on my "helper" sheet I have something like

Company Rep

ABC Computers John Smith

Egghead Sam Talbot

Disks R Us Joe Talco

On my "working" smartsheet I need to be able to enter the information in the Company column and have it pull the Rep information

Company Rep

ABC Computers John Smith

I am trying to do it though a helper sheet because sometimes the Company gets reassigned and would like to just make that update on the helper sheet.

Lauren Kleitz

Using a helper sheet and the index/match function is definitely the way to go for this situation!

Here's how you would set that up!

Make sure the rep column in BOTH your helper sheet and your working sheet are the same column type (e.g.: contact list).

In your working sheet, you are going to make the rep column a column formula that contains the following:

=INDEX({Cross Sheet Reference to Helper Sheet's Rep Column}, MATCH([Company]@row, {Cross Sheet Reference to Helper Sheet's Company Column}, 0))

And, voilà! Rep will populate based on the company name you select. If the company name does not exist in the helper sheet, the formula will throw an error. You can build in error handling as well if you want by using the IFERROR function. If you're interested in that or other things you can do with formula, I highly recommend the formula training in Smartsheet University. Cheers!

TrevRCincySheetz

INDEX/MATCH formula on the working sheet would do the trick.

Lauren Kleitz

Using a helper sheet and the index/match function is definitely the way to go for this situation!

Here's how you would set that up!

Make sure the rep column in BOTH your helper sheet and your working sheet are the same column type (e.g.: contact list).

In your working sheet, you are going to make the rep column a column formula that contains the following:

=INDEX({Cross Sheet Reference to Helper Sheet's Rep Column}, MATCH([Company]@row, {Cross Sheet Reference to Helper Sheet's Company Column}, 0))

And, voilà! Rep will populate based on the company name you select. If the company name does not exist in the helper sheet, the formula will throw an error. You can build in error handling as well if you want by using the IFERROR function. If you're interested in that or other things you can do with formula, I highly recommend the formula training in Smartsheet University. Cheers!

damon.tackett

Thank you @Lauren Kleitz that was EXACTLY what I needed. I had tried it previously with something like that but I didn't have the Rep columns the same format.