# help with formula

#### paul_pearson

##### Board Regular
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

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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!

Replies
2
Views
183
Replies
5
Views
139
Replies
3
Views
193
Replies
9
Views
459
Replies
3
Views
195

1,196,487
Messages
6,015,482
Members
441,898
Latest member
kofafa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back