Find specific data in a column of data and then copy that cell and all data below that into a new column. at a specific location.

[echase]

I am looking for a way to automatically propagate my lookup table.

I would like to enter data in blank cell. Find that data in a column of data and then copy that cell and all data below that into a new column.

The attached spreadsheet is an example of a sheet I use daily in my work.

I use it to convert time codes (HH:MM:SS:FR) from editorial reel length to continuous long play time codes.

Columns A through E is the lookup table and F-N is the conversion section.

Column B represents the time code sequence of 35mm film. The first frame of picture is always at 8 seconds (01:00:08:00) this is cell B3. The film has 24 frames in 1 second as demonstrated in cells B26 and B27.

An editorial reel of film is usually never more than 20 minutes, but it is variable.

Once all the editorial reels are finished and locked in length. They are assembled together to make the continuous long play video.

My task at work is to convert the editorial notes from the editorial work and find their continuous long play equivalent.

I do this by finding the time code of first frame of picture in each of editorial reels in Column B, In this example Reel 2 first frame of picture is 1:17:08:21 (or cell B24504) Selecting to the end of the column, Copying and pasting to column D at cell D3.

I then do the same for Reel 3. FFOP is 1:34:39:16 (cell B49723) pasted to column E at cell E3.

I’m wondering if there is an easier way to make Columns C,D,E instead of copy and paste?

It would be great if I could just enter the First Frame of Picture information and have the column propagate using the sequence in Column B.

Is there a guru out there that can help me with this?
Thanks for listening to my problem and I look forward to any help I could get in this matter.

Ethan Chase.


I tried to figure out how to paste examples. I hope this works.


Example of Lookup table.

A B C D E

Frame	Theatrical	r1	r2	r3
		FFOP R1	FFOP R2	FFOP R3
1	1000800	1000000	1170821	1343916
2	1000801	1000001	1170822	1343917
3	1000802	1000002	1170823	1343918
4	1000803	1000003	1170900	1343919
5	1000804	1000004	1170901	1343920
6	1000805	1000005	1170902	1343921
7	1000806	1000006	1170903	1343922
8	1000807	1000007	1170904	1343923
9	1000808	1000008	1170905	1344000
10	1000809	1000009	1170906	1344001
11	1000810	1000010	1170907	1344002
12	1000811	1000011	1170908	1344003
13	1000812	1000012	1170909	1344004
14	1000813	1000013	1170910	1344005
15	1000814	1000014	1170911	1344006
16	1000815	1000015	1170912	1344007
17	1000816	1000016	1170913	1344008
18	1000817	1000017	1170914	1344009
19	1000818	1000018	1170915	1344010
20	1000819	1000019	1170916	1344011
21	1000820	1000020	1170917	1344012
22	1000821	1000021	1170918	1344013
23	1000822	1000022	1170919	1344014
24	1000823	1000023	1170920	1344015
25	1000900	1000100	1170921	1344016
26	1000901	1000101	1170922	1344017
27	1000902	1000102	1170923	1344018
28	1000903	1000103	1171000	1344019
29	1000904	1000104	1171001	1344020
30	1000905	1000105	1171002	1344021
31	1000906	1000106	1171003	1344022
32	1000907	1000107	1171004	1344023
33	1000908	1000108	1171005	1344100
34	1000909	1000109	1171006	1344101
35	1000910	1000110	1171007	1344102
36	1000911	1000111	1171008	1344103
37	1000912	1000112	1171009	1344104
38	1000913	1000113	1171010	1344105
39	1000914	1000114	1171011	1344106
40	1000915	1000115	1171012	1344107
41	1000916	1000116	1171013	1344108
42	1000917	1000117	1171014	1344109
43	1000918	1000118	1171015	1344110
44	1000919	1000119	1171016	1344111
45	1000920	1000120	1171017	1344112
46	1000921	1000121	1171018	1344113
47	1000922	1000122	1171019	1344114
48	1000923	1000123	1171020	1344115

<!--StartFragment--> <colgroup><col width="65" span="5" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

24501	1170820	1170020	1340917	1514012
24502	1170821	1170021	1340918	1514013
24503	1170822	1170022	1340919	1514014
24504	1170823	1170023	1340920	1514015
24505	1170900	1170100	1340921	1514016
24506	1170901	1170101	1340922	1514017

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

EXAMPLE OF THE CONVERSION SECTION FOR REEL 2 ONLY:
I J K

R2	R2	R2
THEAT. LOC.	REEL OFFSET	LP EQUIV.
2000000	=I3-1000000	=VLOOKUP(J3,B:E,3,TRUE)
2000600	=I4-1000000	=VLOOKUP(J4,B:E,3,TRUE)
2000800	=I5-1000000	=VLOOKUP(J5,B:E,3,TRUE)
2173819	=I6-1000000	=VLOOKUP(J6,B:E,3,TRUE)
	=I7-1000000	=VLOOKUP(J7,B:E,3,TRUE)
	=I8-1000000	=VLOOKUP(J8,B:E,3,TRUE)
	=I9-1000000	=VLOOKUP(J9,B:E,3,TRUE)

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[echase]

I have resolved this problem. As soon as I can figure out how to paste my spreadsheet I will share the answer. Thanks echase