IF OR Formula change

[julhs]

There is 2 parts to my query.
1st, is there a better way to write the IF OR formula I have got in A9.
2nd, when I insert a row (in say C11) how can I ensure the formula in A9 adjusts to include the NEW row.
Because as it stands at the moment, (part of A9 formula) is:

IF(OR(A10=C7, A10=C8, A10=C9, A10=C10, A10=C11),"Supplier #1 (Spain)"

But when I insert a row in C11 the above automatically changes to:

IF(OR(A10=C7, A10=C8, A10=C9, A10=C10, A10=C12),"Supplier #1 (Spain)"

so formula is missing A10=C11

I have added a Mini sheet with a Validation List on it just to assist. HOWEVER in my real situation the VD list is in (Source)WB and that selected value would be brought into A10 by using:

A10='[Source.xlsm]Sheet1 '!$D$20

Data.xlsm
	A	B	C	D
1
2
3
4
5
6			Supplier #1 (Spain)
7			Green Grapes
8			Red Grapes
9	Supplier #2 (Spain)		Oranges
10	Cucumber		Strawberry
11
12			Supplier #2 (Spain)
13	Data Validation List = $C$6:$C$24		Cucumber
14			Lettice
15			Tomatoes
16			Green Peppers
17			Red Peppers
18
19			Supplier #3 (Kenya)
20			Green beans
21			Kiwi
22			Pomegranate
23
Sheet3

Cell Formulas
Range	Formula
A9	A9	=IF(A10=0,"", IF(OR(A10=C7,A10=C8,A10=C9,A10=C10,A10=C11),"Supplier #1 (Spain)", IF(OR(A10=C13,A10=C14,A10=C15,A10=C16,A10=C17,A10=C18),"Supplier #2 (Spain)",IF(OR(A10=C20,A10=C21,A10=C22,A10=C23),"Supplier #3 (Kenya)", "Check selection"))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A9	Cell Value	contains "Check selection"	text	NO
A10	Expression	=$A$10>""	text	NO
A9	Expression	=$A$9>""	text	NO
C7:C23	Expression	=IF($A$10>"",$A$10=$C7:$C$23)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A10	List	=$C$6:$C$22

 

[StephenCrump]

What is your formula in A9 going to look like when you have 10 Suppliers? Or 100 Suppliers.

I'd put all the suppliers into one lookup table:

	A	B	C	D	E
1				Supplier
2	Cucumber	#2 (Spain)		Cucumber	#2 (Spain)
3				Green beans	#3 (Kenya)
4				Green Grapes	#1 (Spain)
5				Green Peppers	#2 (Spain)
6				Kiwi	#3 (Kenya)
7				Lettuce	#2 (Spain)
8				Oranges	#1 (Spain)
9				Pomegranate	#3 (Kenya)
10				Red Peppers	#2 (Spain)
11				Red Grapes	#1 (Spain)
12				Strawberry	#1 (Spain)
13				Tomatoes	#2 (Spain)
Sheet1

Cell Formulas
Range	Formula
B2	B2	=VLOOKUP(A2,Supplier,2,)

Named Ranges
Name	Refers To	Cells
Supplier	=Sheet3!$D$2:$E$13	B2

And I'd put the Supplier table somewhere I didn't need to insert or delete rows. I usually use a separate worksheet for all my tables.

 

[julhs]

Thank you for the advice Stephen.
The list of “Suppliers” in #1 Xl2bb was only a scaled down mock-up of the actual Source WB.
In real time there are 90 rows and 30 columns in the Source WB containing additional data that is referenced by umpteen other formulas.
Redoing the layout to all that is a HUGH task and it can’t be justified, but one way or other products will need to be added to the list!

So that said; your use of VLOOKUP in A9 and the alphabetical way you listed the products gave me the idea of just adding a hidden column (D:D) containing the supplier name to my existing layout and use VLOOKUP in A9 as you suggested.
I fully except it‘s a clumsy way of doing things but it was simple enough to do and avoided the need for major redo of layout to my actual Source WB.
Many thanks for your help

Source.xlsm
	A	B	C	D	E
1
2
3
4
5
6			Supplier #1 (Spain)	Supplier
7			Green Grapes	#1 (Spain)
8			Red Grapes	#1 (Spain)
9	#2 (Spain)		Oranges	#1 (Spain)
10	Tomatoes		Strawberry	#1 (Spain)
11			Blueberry	#1 (Spain)
12
13			Supplier #2 (Spain)
14			Cucumber	#2 (Spain)
15			Lettice	#2 (Spain)
16			Tomatoes	#2 (Spain)
17			Green Peppers	#2 (Spain)
18			Red Peppers	#2 (Spain)
19
20			Supplier #3 (Kenya)
21			Green beans	#3 (Kenya)
22			Kiwi	#3 (Kenya)
23			Pomegranate	#3 (Kenya)
24
Sheet3

Cell Formulas
Range	Formula
A9	A9	=VLOOKUP(A10,Supplier,2,)

Named Ranges
Name	Refers To	Cells
Supplier	=Sheet3!$C$6:$D$23	A9

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A10	Expression	=$A$10>""	text	NO
A9	Expression	=$A$9>""	text	NO
C7:C24	Expression	=IF($A$10>"",$A$10=$C7:$C$24)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A10	List	=$C$6:$C$23

 

[Alex Blakenburg]

You might want to consider using =VLOOKUP(A10,Supplier,2, FALSE) or =VLOOKUP(A10,Supplier,2,0) which is much clearer for most people.
Not many people will know that =VLOOKUP(A10,Supplier,2,) defaults to False but =VLOOKUP(A10,Supplier,2) without the comma defaults to True.

 

[julhs]

Thanks for the tip Alex

 

[StephenCrump]

So that said; your use of VLOOKUP in A9 and the alphabetical way you listed the products gave me the idea of just adding a hidden column (D:D) containing the supplier name to my existing layout and use VLOOKUP in A9 as you suggested.

Sounds like a good solution. I'm glad we could point you in the right direction.