Hi Jason,

You can't enter a dynamic range formula directly into the data validation source field. You must create a dynamic named range and use that name in your data validation list source field.

In regards to your first statement, please provide an example file so I can see the formula in context.

Thanks,

Mynda

Thank you very much, I missed that one step.  As soon as I saw your response I remembered you had mentioned that before in the course.  And I was able to get it to work in my workbook.

As for the formula not returning the #VALUE!, I have attached a file with the example purely for you to look at out of curiosity, I don't need a response.  The formula will return the #VALUE! if not on the same line as the data as you can see in the file.  I.E.  if the formula is =A1:A10 and is written in cell C1 I get the value in cell A1, however if the formula is in cell C15 then it returns the #VALUE!. 

Thanks for the feedback.  I'm learning so much from this course. Keep up the great work.

Jason

Hi Jason,

Glad you got it working.

In regards to your #VALUE error in cell C9 vs the formula resolving to a single value when it's in cell C1; this is known as implicit intersection and it's explained in this tutorial: https://www.myonlinetraininghub.com/excel-implicit-intersection

An aside, the formula in cell C1 is =$A$1:$A$7. i.e. 7 cells, but Excel can only display a single value in each cell and since you haven't told it to do anything with that range (e.g. SUM, AVERAGE, COUNT etc.), it simply returns a single value based on the corresponding row.

This will make more sense after reading the link above, and for others reading this thread, please download Jason's workbook so you can see what we're talking about in context.

Mynda