Think this is what you want, just wanting to use an existing range as a bunch of options to choose from elsewhere right?

https://support.microsoft.com/en-us/office/create-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407b

Probably know already but you’ll likely want to do the $ protect range thing for when you’re dragging across a load of cells. Or you could just highlight the entirety of the row(s) from the off and do data validation on them

@Mosoona - Yeah, but with a filtered drop down list dependent on the Product

@quest - Set up as below:

https://wetransfer.com/downloads/08bfd7fd7680088dd2016de4b019a90d20220629130221/d3d38be71e844bf9318333134f5a381220220629130232/2da9f9

Updated with notes

premium bonds

eat the rich

No, that's average performance.

this only works with Office365:
1. set up helper array somewhere (e.g. a different sheet) which will have a list per product.
to do this you can do =FILTER(Products[Batch Code],Products[Product]=A14) and then fill across to make a filtered list for each product. This is asking for a filtered list of batch codes where the product matches A14.

2. set the data validation list for the Apple column to =helper!A1# where A1 is the location of the first helper array. The # symbol tells excel to look at the array starting at A1, rather than cell A1.

3. you can fill across the apple columns to other columns and the validation will look in B14, C14 etc.

Are you an engineer?

Will be quite tricky without a competing offer. That's your usual leverage.

What. A. Cunt.

me or him? i am willing to be challenged. i still have all the power to amend the review