How to copy an offset datavalidation to multiple rows

Hi
I created a dynamic data validation to provide a fixed options list based on the previous cell input, i.e. I need to change the S$28 in the MATCH and COUNTIF as per the example below to match the cell it relates to when going down the full column. As you can imagine this takes a long time so any options would be much appreciated.

=OFFSET(B7,MATCH($S$28,$A$8:$A$22,0),0,COUNTIF(A8:A22,$S$28),1)

I am not knowledgeable in using VBa but will try anything rather than writing this formula in every cell but it does provide the exact option I need.

Thanks

Hi Ford
I have put together a sample of what I am doing with the Offset but cant find a way to upload it. Being new to the forum I am still finding my way around, can you tell me where the upload option is?

Thanks

Hi Ford
I have put together a sample of what I am doing with the Offset but cant find a way to upload it. Being new to the forum I am still finding my way around, can you tell me where the upload option is?

Thanks

Thanks for your help, its giving me a an error "You may not use unions, Intersections, or any constants for Data Validation criteria"

Lost my reply to you FDibbins, that looks like the fix.. Is it due to me loading $C$7 rather than C7? the only one that doesn't work for some reason is the WITH SALES, as the validation cell is blank can you tell me if i'm missing something as this will work for me. really appreciate the input.

Love it.. thanks for the learning.