insert text in fields (possibly mulitple) if certain conditions met

Please help.

I have attached a spreadsheet template that we use to track deliveries into our warehouse and the supplier's performance. The sheet is manually completed and records delivery issues; comments and scores suppliers accordingly. This is used by more than one department.

We have agreed exceptions for certain suppliers - certain standards that they do not have to meet. I need an easy and automatic way of showing the agreed exceptions when the spreadsheet is completed.

Supplier's names are on a drop down list, so ideally, when a supplier's name is selected, the list of delivery issues will adjust so the agreed exceptions for that particular supplier will be clear. Ideally, the magic trick will insert an N/A into the fields as applicable.

However, I have no clue if this can even be done.

We have approx 75 different supplier names (and this list can grow), and currently 14 approved exceptions -some suppliers have no agreed exceptions, so all fields could apply to them; others have one or more agreed exceptions. We are currently checking manually which is extremely time consuming. There has to be an easier way.

I muddle my way through things like this, but this one has got me stumped. No idea how to even begin.

If anyone can assist with this I would be very grateful.

Many thanks in advance

Jen

Hi Jen -

The Forum Moderators don't like thread titles like the one you've posted "I Have No Clue How To Do This". They are trying to build a searchable database so other people with your same problem can query past results and hopefully find answers on their own. Your title creates a problem in the searchability (I'm not sure that's actually a word ;-) ) of the database.

So, can you use the thread tools to revise your title to something like "Help with VLOOKUP"? I glanced through your attached spreadsheet and I think that's where your solution will be. Technically, I get in trouble if I help you with a bad thread title.

Thanks,

Hi -

OK - I think that's a reasonable enough title. If I understand what you are trying to do, I think VLOOKUP is your solution. Just copy and paste this formula into cell X5:

=VLOOKUP($D5,'AGREED EXCEPTIONS'!$A$3:$O$200,2,FALSE)

This formula looks at the supplier name in cell D5 and checks it against your exceptions list in the range of A3 to O200 (I added extra rows in case you add more suppliers). A3 is considered Column 1 for VLOOKUP, so B3 is Column 2, which is the same category "No Separation" on Sheet 2. You then need to copy this formula across to your other cells you want to check (Z5, AB5, AD5, etc.) and increment the column number to return in the VLOOKUP formula. So, for example, if you copy the above fromula to Cell Z5 of Sheet 1, just edit the formula column number to return to 3. It will look like this:

=VLOOKUP($D5,'AGREED EXCEPTIONS'!$A$3:$O$200,3,FALSE)

You can see everything is the same except the column number is 3 instead of 2.

Hope this helps!

One more thing -

Once you get the cells edited all the way across the first line, you can just copy them straight down.

Sorry - third thing -

I reread your requirements and you wanted "N/A" if no X was found. Just revise the formula like this:

=IF(VLOOKUP($D5,'AGREED EXCEPTIONS'!$A$3:$O$200,2,FALSE)="X","X","N/A")

All the other instructions still apply.

Sorry for the oversight.

The Forum Moderators don't like thread titles like the one you've posted "I Have No Clue How To Do This".

Just for clarification and for the avoidance of misconception: it is not the forum moderators who decide upon the rules here. We are here only to uphold them. There is a perfectly reasonable explanation for this particular rule, to which we ALL agreed upon signing up here.

Hi Jen -

I'm not sure I understand. Can you give me an example? Thanks.