[SOLVED] Dropdown list formatting with range via formula

[platyhiker]

I have a cell with a dropdown list (created via Data -> Validity and using a Cell range for allowable values), and the values are Dates. When I directly specify the cell range (e.g. $AB$1:$CA$1 ) everything looks great and the values are shown in the drop down list as dates. If I get fancier and try to specify the cell range via a formula (e.g. INDIRECT("$AB$1:$CA$1") ), then the values in the list are shown as integers not dates. Is there a way to use a formula AND have the formatting of the cell range get used for the display of the date range??? (The cell is formatted to show Date values; after selecting an integer from the dropdown list, it is displayed as a Date. All the cells in the cell range are formatted to show Date values.)

Background: I want to have the list of items displayed in the dropdown list depend on other values in the spreadsheet. Sometimes I want to show all 52 possible values, sometimes I want to show fewer values, leaving off the first n values. The only way I figured out how to do this is to calculate what set of values I want, then generate the start and end addresses via ADDRESS(), concatenate the start address, ":", and the end address to have text for the desired cell range, and then use INDIRECT() to convert the text back to a cell range. If there's a better way to calculate a cell range in a formula, I'd be delighted to learn of it.

[FJCC]

The OFFSET() function might be easier to use.
A formula of this form seems to work.

Code: Select all

TEXT(INDIRECT("$A$1:$D$1");"M/D/YYYY")

[platyhiker]

Thanks so much! Using the TEXT() function with the appropriate formatting string gave me just what I wanted.

Additionally, using the OFFSET() function is definitely easier to work with for my purposes. For anybody reading this far, my formula ended up being:
TEXT(OFFSET(AB1;0;X14;1;52-X14);"MM/DD/YYYY") where cell X14 holds the number of cells at the beginning of the range that I do *not* want displayed in the drop down list.