Formula copy date down from another cell

L

Lobsterboy1

Board Regular
Joined
Aug 5, 2016
Messages
90
Hi, I am struggling to find a formula to do what I want which I am sure is not hard at all but just cant get my head round it. I have a very large set of data, every so many rows in column D there is a date, the row number will be completely random, just looking at the first few they are cells D2,D8,D12,D22,D28,D53 and so on for nearly 200000 rows. What I am trying to do is put a formula in column A which will copy the date down looking at the values in column D. So if D2 is the 01/07/20 put this date in cells A2 to A7 then if D8 is the 02/07/20 put this in cells A8 to D11 and so on down column A. Column D contains other data not just dates which is where I am struggling. I have been trying to use column B to help me as in this column it will always have "DELIVERY DATE" on the same row as the actual date. So B2,B8,B12,B22,B28,B53 Will always have "DELIVERY DATE" and so on. Any ideas would be great.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Try these, the 1st one depends on whether there are other numerical values in Col D, if there are it won't work.

=IF(ISNUMBER(D3),D3,C2)

=IF(B3="Delivery Date",D3,C2)
 
Last edited:
Upvote 0
Hi Gaz, here is a sample of the data in column D

year to date del.xlsx
D
206/01/2020
3Batch No
4417
5417
6417
7417
806/01/2020
9Batch No
10417
11417
1206/01/2020
13Batch No
14416
15416
16416
17416
18416
19416
20416
21416
2206/01/2020
23Batch No
24417
25417
26417
27417
6 months del


I will try the other formula.

Thanks.
 
Upvote 0
2nd one should work, 1st won't as there are other numbers in Col D.

=IF(B3="Delivery Date",D3,C2)
 
Upvote 0
Tried the formula, I changed it slightly as column C is nothing to do with what I need. Here is what it gave me.
year to date del.xlsx
ABCD
206/01/2020Delivery Date06/01/2020
300/01/1900Item Batch No
401/01/190011417
501/01/190021417
601/01/190031417
701/01/190041417
806/01/2020Delivery Date06/01/2020
900/01/1900Item Batch No
1001/01/190011417
1101/01/190021417
1206/01/2020Delivery Date06/01/2020
1300/01/1900Item Batch No
1401/01/190011416
1501/01/190021416
1601/01/190031416
1701/01/190041416
1801/01/190051416
1901/01/190061416
2001/01/190071416
2101/01/190081416
2206/01/2020Delivery Date06/01/2020
2300/01/1900Item Batch No
2406/01/190016417
2506/01/190026417
2606/01/190036417
2706/01/190046417
2806/01/2020Delivery Date06/01/2020
6 months del
Cell Formulas
RangeFormula
A2:A28A2=IF(B2="Delivery Date",D2,C2)


Thanks.
 
Upvote 0
I have manually written in the dates in this example but need a formula to do it.

year to date del.xlsx
ABCD
13206/01/2020Delivery Date06/01/2020
13306/01/2020Item Batch No
13406/01/202011416
13506/01/202021416
13606/01/202031416
13706/01/202041416
13806/01/202051416
13906/01/202061416
14006/01/202071416
14106/01/202081416
14206/01/202091416
14306/01/2020101416
14406/01/2020111416
14506/01/2020121416
14606/01/2020131416
14706/01/2020141416
14806/01/2020151416
14906/01/2020161416
15006/01/2020171416
15106/01/2020181416
15206/01/2020191416
15306/01/2020201416
15406/01/2020211416
15506/01/2020221416
15606/01/2020231416
15706/01/2020241416
15806/01/2020251416
15906/01/2020261416
16006/01/2020271416
16106/01/2020281416
16206/01/2020291416
16306/01/2020301416
16406/01/2020311416
16506/01/2020321416
16606/01/2020331416
16706/01/2020341418
16806/01/2020351418
16906/01/2020361418
17006/01/2020371418
17107/01/2020Delivery Date07/01/2020
17207/01/2020Item Batch No
17307/01/202011436
17407/01/202021436
17507/01/202032436
17607/01/202042436
17707/01/202051436
17807/01/202061436
17907/01/202071436
18007/01/202081436
18107/01/202091436
18207/01/2020101436
18307/01/2020111436
18407/01/2020121436
18507/01/2020131436
18607/01/2020141436
18707/01/2020151436
18807/01/2020161436
18907/01/2020Delivery Date07/01/2020
6 months del


Thanks.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
188
bh24524
B
sanantonio
  • Solved
Returning next Date once Date has passed
Replies
5
Views
324
dreid1011
dreid1011
D
  • Solved
VBA check whether a row for the current date has been filled in and return yes/no value in another cell.
Replies
5
Views
208
mumps
mumps
C
  • Question
Dependent Drop Down Help, Excel 2010
Replies
5
Views
149
Akuini
Akuini
J
  • Solved
Copying the data from another spreadsheet for specific product
Replies
2
Views
128
junaidaun
J

Forum statistics

Threads
1,215,008
Messages
6,122,672
Members
449,091
Latest member
peppernaut

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top