IFs, Index/Match and Check Boxes

Becky Wilson

I have 2 sheets:

Sheet 1 - uses a form for initial information. Additional columns exist to provide resolution information, including assigned to (contact list) and closed (checkbox)

Sheet 2 - used to lookup values to populate the assigned to column on Sheet 1 based on the Region provided via the form.

I am using Index(Match) to populate the assigned to column using the data from Sheet 2. However, I only want it to look at sheet 2 for the data if closed on sheet 1 is unchecked. If the closed is checked I want it to leave the current data in the assigned to column as is (no changes)

I tried the following formula:

=IF(Closed@row = 0, INDEX({Region}, MATCH(Region@row, {Name}), 0)). When closed is checked it changes the value in the Assigned to column to blank.

Thoughts?

Mike TV

@Becky Wilson

Your IF formula only tells your sheet what to do if Closed@row = 0. You forgot to add in the false value. Just before the final ) put a comma and then tell it where to get the value from if Closed@row does not equal 0.

Becky Wilson

I'm not sure that will work. I want the data in the assigned to column to stay as is, do nothing when or if the box is checked.

Mike TV

@Becky Wilson

We're going to need a screenshot of at least one and maybe both sheets (without sensitive data). It sounds like you're putting the formula in one sheet, referring to the Assigned To in another sheet but only if Closed = 0. Because the location of your formula is blank without an index match to fill it out, the result of course will be blank. What else could it be?

Becky Wilson

Here is sheet 1:

Here is Sheet 2

When the region is populated on Sheet 1 - there is an Index Match that looks up region on Sheet 2 and returns the Name to the Regional Champion (assigned to) column on Sheet 1. I only want this to happen if the Closed check box is not checked.

If the Closed check box is checked, I want the current regional champion to remain as is.

Mike TV

@Becky Wilson

I can see where you're getting your Regional Champion from if Closed = 0. It also looks like the formula you're working on is in the Regional Champion column on Sheet 1, correct?

So where on either sheet is the Regional Champion supposed to be grabbed from when Closed = 1? It can't be the Regional Champion column on sheet 1 because that is a formula and hence blank if not index matching when Closed = 0.

If it's a static person (meaning always a certain person if Closed = 1), then you can tell it what to put in the Regional Champion column by something like this:

=IF(Closed@row = 0, INDEX({Region}, MATCH(Region@row, {Name}), 0), "Paul Jones")

Becky Wilson

Not a static person. that is part of my dilemma.

The regional person could change (so details on sheet 2 change), but we don't want them to change on sheet 1 if the closed is checked.

So I'm not sure if a helper column on sheet 2 that get's locked down. I know if it is on sheet one it creates a circular reference

Mike TV

@Becky Wilson

Ok, but where is it supposed to be grabbed from when Closed = 1? There's no way to help if you don't provide that information.

Becky Wilson

I think I figured this out. If I had an Original Champion column in sheet 2, then if the box is checked I can have it pull that data.

Thanks for your patience and support.

Mike TV

@Becky Wilson

Exactly. You have to point it somewhere. I guess you were trying to point it to a location that you hadn't created yet lol.

Becky Wilson

I was hoping not to have to point it anywhere - just leave as is.