Data validation based on Sheet Name

[SuperX7]

Hi guys,

Been racking my head and finally have succumbed to making a post, normally I can find / piece together solutions by lots of google and experimenting but I'm throwing in the towel this time.

I'm trying to create a sheet where the user selects a supplier in column A (options predetermined by another sheet), and depending on the supplier selected, a list of ingredients is available via a list format data validation in column B.

Eg. User selects supplier 1, then the list looks for sheet called "supplier 1" and gives a list of ingredients a user can choose from in Column B (which in screenshot example below is products 1 - 10)

Thanks for your time in looking into this in advance!

 

[kevin9999]

The trick is in the data validation List formula. This assumes the selections in column A of the Main sheet are spelt exactly the same as the sheet names.

Book1
	A	B
1	Supplier	Ingredient
2	Supplier 1
3
Main

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=$D$1:$D$3
B2	List	=OFFSET(INDIRECT("'"&A2&"'!B2"),0,0,COUNTA(INDIRECT("'"&A2&"'!B:B"))-1,1)

 

[SuperX7]

Thanks so mich Kevin... that's amazing and works a treat

Would you mind explaining the workings of the formula? Teach a man to fish... as they say

 

[kevin9999]

The OFFSET function works like this: OFFSET(<starting point> , <rows from here> , <columns from here> , <height of range in rows> , <width of range in columns>)
For example, this: =OFFSET(A1, 1, 1, 5, 2) would mean starting from cell A1, move down one row and across one column (so you’re now in B2) set the range from this new point to be 5 rows x 2 columns – so you’ve selected the range B2:C6.

The INDIRECT function gives you a reference based on the contents of a cell. So for example, =INDIRECT(A1) would return whatever is in the cell/range that is referred to in cell A1. If cell A1 contained “B50” then the INDIRECT function would return – not what was in cell A1, but what was in cell B50.

The COUNTA function simply counts the number of non-blank cells in the range selected. In the solution, column B (B:B) is referenced – but starting from cell B2, so to exclude B1 from the ‘count’ there is a minus 1 (-1).

References:
OFFSET function - Microsoft Support
INDIRECT function - Microsoft Support
COUNTA function - Microsoft Support.

 

[SuperX7]

The OFFSET function works like this: OFFSET(<starting point> , <rows from here> , <columns from here> , <height of range in rows> , <width of range in columns>)
For example, this: =OFFSET(A1, 1, 1, 5, 2) would mean starting from cell A1, move down one row and across one column (so you’re now in B2) set the range from this new point to be 5 rows x 2 columns – so you’ve selected the range B2:C6.

The INDIRECT function gives you a reference based on the contents of a cell. So for example, =INDIRECT(A1) would return whatever is in the cell/range that is referred to in cell A1. If cell A1 contained “B50” then the INDIRECT function would return – not what was in cell A1, but what was in cell B50.

The COUNTA function simply counts the number of non-blank cells in the range selected. In the solution, column B (B:B) is referenced – but starting from cell B2, so to exclude B1 from the ‘count’ there is a minus 1 (-1).

References:
OFFSET function - Microsoft Support
INDIRECT function - Microsoft Support
COUNTA function - Microsoft Support.

Thanks! Which part of the formula understands to look within the sheet and not a cell value?

 

[SuperX7]

Thanks! Which part of the formula understands to look within the sheet and not a cell value?

Nevermind! I understand the indirect function now