Excel – Dropdownlist works in Excel 2010 but not 2007

According to the link below and my personal testing of it, you must enter the data series in the form of

'WorkbookName.xls'!RangeNameX

If your name is dynamic, then the resulting chart will be too. The formula I used for my dynamic named range was (modify for your situation and use for both X and Y ranges).

=OFFSET($A$1,0,0,MATCH(1E+306,$A:$A,1),1)

That worked to make the chart dynamic as far as adding newly appended values to the chart (required both X and Y values before chart updated).

Source for how to Setup Chart - Microsoft Q&A Site Source for hot to use Dynamic Ranges - OZGrid

Data validation (and conditional formatting) references can point to a different sheet in Excel 2010 and later. In versions earlier than 2010, though, this will not work.

You will need to create a named range for the cells that feed the data validation. So, in the sheet with the list of values, select the values and click the Formulas ribbon > Name Manager > New > enter a name for the range (or use your favourite technique to create a named range). Then set the data validation to the list option and let it refer to the range name, like

=MyRange

(where "MyRange" is the name of the range you just created) This will work in all current versions of Excel.