help with formula

[paul_pearson]

Hi

How does the following formula work please

V1 has the =TODAY()+21*ROW()-21

Data Validation:
=OFFSET($V$1,0,0,COUNTA($V:$V),1)

Thanks

Paul
Excel 2010

<!-- google_ad_section_end -->

 

[saelageonzon]

This means that the data validation is a specified dynamic range. You can check this under named range (Press Ctrl+F3) and find the named range whose range refers to the offset formula.

The formula "=OFFSET($V$1,0,0,COUNTA($V:$V),1)" means that from Cell V1, it will return an array (or range of cells) depending on how many non blank cells there are. The data validation will adjust in case you add values within column V.

The formula "=TODAY()+21*ROW()-21" returns the dates with the interval of 21 days after today. So from cell V1, which is today, the row after that will be 21 days from today. Two rows after V1 will be 42 days after today and so on.

You can copy the formula until your desired number of rows. The dates with intervals of 21 will be returned as a selection on your data validation.

Hope this works!