What is the best way to reference data from another sheet to fill in a table on a separate sheet

[biggynbp]

I am trying to create a table to better read the commitment of traders (COT) report. Ive used vllookup to pull from the table to fill in the fields. .The problem I am facing is when I have to update the sheet. I tried to add new columns but found that i have to redo the entire spreadsheet. Does anyone have a way to update the sheet from the source info without having to recreate it each time.

COT Report.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1	Currency	Date	Long	Short	NON-COM Change Long	NON-COM Change Short	% Long	% Short	Net Position	Open Interest	OI change
2	DXY	11/15/2022	38,965	12,111	(3,922)	(583)	81%	25%	26,854	48,382	(7,958)
3	DXY	11/8/2022	42,887	12,694	583	178	76%	23%	30,193	56,340	398
4	DXY	11/1/2022	42,304	12,516	(2,694)	(2,384)	76%	22%	29,788	55,942	(3,111)
5	DXY	10/25/2022	44,998	14,900	42	2,634	76%	25%	30,098	59,053	3,819
6	DXY	10/18/2022	44,956	12,266	(123)	(27)	81%	22%	32,690	55,234	619
7	DXY	10/11/2022	45,079	12,293	630	(459)	83%	23%	32,786	54,615	(23)
8	DXY	10/4/2022	44,449	12,752	(2,975)	(4,110)	81%	23%	31,697	54,638	(1,408)
9	DXY	9/27/2022	47,424	16,862	1,720	(911)	85%	30%	30,562	56,046	1,754
10	DXY	9/20/2022	45,704	17,773	(3,280)	4,458	84%	33%	27,931	54,292	(7,785)
11	DXY	9/13/2022	48,984	13,315	(3,623)	(3,185)	79%	21%	35,669	62,077	415
12	DXY	9/6/2022	52,607	16,500	935	343	85%	27%	36,107	61,662	1,478
13	DXY	8/30/2022	51,672	16,157	2,361	1,945	86%	27%	35,515	60,184	2,576
14	DXY	8/23/2022	49,311	14,212	(1,471)	1,391	86%	25%	35,099	57,608	(1,301)
15	DXY	8/16/2022	50,782	12,821	44	720	86%	22%	37,961	58,909	(730)
16	DXY	8/9/2022	50,738	12,101	2,820	3,530	85%	20%	38,637	59,639	2,585
17	DXY	8/2/2022	47,918	8,571	(3,419)	(2,231)	84%	15%	39,347	57,054	(3,402)
18	DXY	7/26/2022	51,337	10,802	1,103	(363)	85%	18%	40,535	60,456	1,242
19	DXY	7/19/2022	50,234	11,165	(875)	(1,590)	85%	19%	39,069	59,214	(351)
20	DXY	7/12/2022	51,109	12,755	(1,818)	(921)	86%	21%	38,354	59,565	(1,292)
Info

Cell Formulas
Range	Formula
J2:J20	J2	=D:D-E:E

COT Report.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Currency	Nasdaq
2
3	Date	Long	Short	NON-COM Change Long	NON-COM Change Short	% Long	% Short	Net Position	Open Interest	OI change
4	11/15/2022	#N/A
5	11/8/2022
6	11/1/2022
7	10/25/2022
8	10/18/2022
9	10/11/2022
10	10/4/2022
11	9/27/2022
12	9/20/2022
13	9/13/2022
14	9/6/2022
15	8/30/2022
16	8/23/2022
17	8/16/2022
Test

Cell Formulas
Range	Formula
B4	B4	=INDEX(Info!B2:L185,MATCH(Test!B1&Test!A4,0))

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=LKUP!$A$1:$A$4

 

[jdellasala]

Before proceeding, removed the Array formula "=D:D-E:E". Then, with any cell in the data's range hit [Ctrl]T to turn it into an Excel Table. Give the table a name like COT in the Table Name field at the top left of the Table Design ribbon. Now go to the first blank row of the Net Position column and type an "=" then left arrow to the Long column, type a minus, and then arrow to the Short column and hit [Enter]. You should end up with the formula:

=[@[ Long ]]-[@[ Short ]]

AND the formula will automatically spill down. New rows added to the table will automatically be included in calculations as well as the scope of any table references.

The reason I went through that is because once you've done that, you can reference the entire table including headers using

=COT[#All]

That includes headers and all the data spilling from a single cell.

Columns of data can be referenced like this

=COT[Currency]

, individual headers like this

=COT[[#Headers],[Currency]]

, and formulas are easier to create, understand, and maintain because the Intellisense will automatically suggest values resulting in formulas like this

=XLOOKUP(P25,COT[Date],COT[[ Net Position ]])

. Notice I don't even have to tell you what I was looking up or what I wanted returned.

I think using an Excel Table will solve a lot of your problems.