utilizing OFFSET formula inside a formula to transpose list

[jjp2985]

I am having a little issue with utilizing the OFFSET formula in my worksheet as well as understanding how it works in the context how I want to use it. I tried to follow some information on the web but cannot figure out how to get it to work as the part I'm having a hard time understanding is within the OFFSET formula they are using COLUMN()-2 or ROW()-3 for which none go into explaining what those arguments do.

In Cell I18 I want to utilize the OFFSET function utilizing the list in cells A21:A31. This formula will get copied across the row as you can see the different type of caps in those headings so for each column it goes over I want it to reference the next item down the list from A21:A31. The OFFSET would go at two positions in the formula that is already in cell I18 and would replace the $A$21 in that formula.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
15	Pack Type	1 single pack has 100 caps in it						1 Large bag has 10 single pack of caps in it						100 green caps = 10g
16	Large Bag	Date	Initials	Department	Cap Type	Pack Type	Number of Pack(s)/Cap(s) Took/Received	Amount Small Packs Took	Remaing 2mL Prep Vial Black Cap Small Packs in Inventory	Remaing GC Vial Cap Small Packs in Inventory	Remaing 4mL Prep Vial Cap Small Packs in Inventory	Remaing Test Tube Cap Packs in Inventory	Remaing LC Vial Cap Small Packs in Inventory	Remaing 2mL Prep Vial Green Caps in Inventory
17	Single Pack	12-Mar-2021	JJP	Inventory	GC Vial Cap			1536	1536	1823	1158
18	Microcentrifuge Cap Bag	23-Feb-2021	JJP	Commercial	2mL Prep Vial Black Cap	Large Bag	5	50	1486	1823	1158
19		21-Jan-2021		Research	4mL Glass Vial Cap	Large Bag	11	110	1486	1823	1048
20	Cap Type	12-Feb-2021		R&D	2mL Prep Vial Black Cap	Large Bag	23	230	1256	1823	1048
21	2mL Prep Vial Black Cap	11-Jan-2021		UK	GC Vial Cap	Large Bag	42	420	1256	1403	1048
22	GC Vial Cap	21-Feb-2021		Australia	4mL Glass Vial Cap	Large Bag	32	320	1256	1403	728
23	4mL Glass Vial Cap	2-Apr-2021		Inventory	GC Vial Cap	Large Bag	200	2000	1256	3403	728
24	Test Tube Cap	3-May-2021		Commercial	2mL Prep Vial Black Cap	Large Bag	5	50	1206	3403	728
25	LC Vial Cap	2-Jun-2021		Research	4mL Glass Vial Cap	Large Bag	11	110	1206	3403	618
26	2mL Prep Vial Green Cap	5-Apr-2021		R&D	2mL Prep Vial Black Cap	Large Bag	23	230	976	3403	618
27	Microcentrifuge Tube Red Caps	5-Apr-2021		UK	GC Vial Cap	Large Bag	42	420	976	2983	618
28	Microcentrifuge Tube Yellow Caps	4-May-2021		Australia	4mL Glass Vial Cap	Large Bag	32	320	976	2983	298
29	Microcentrifuge Tube Purple Caps	3-Jul-2021		Inventory	GC Vial Cap	Large Bag	200	2000	976	4983	298
30	Microcentrifuge Tube Orange Caps	5-Jul-2021		Commercial	2mL Prep Vial Black Cap	Large Bag	5	50	926	4983	298
31	Microcentrifuge Tube Clear Caps	3-Aug-2021		Research	4mL Glass Vial Cap	Large Bag	11	110	926	4983	188
32		4-Sep-2021		R&D	2mL Prep Vial Black Cap	Large Bag	23	230	696	4983	188
33		5-Sep-2021		UK	GC Vial Cap	Large Bag	42	420	696	4563	188
Caps for Vials

Cell Formulas
Range	Formula
H18:H33	H18	=IF(F18=$A$16,G18*10,IF(F18=$A$18,G18*500,IF(F18=""," ",G18)))
I18:I33	I18	=IF(AND(D18="Inventory",E18=$A$21),I17+H18,IF(OR(D18="",E18=""),"",IF(E18=$A$21,I17-H18,I17)))
J18:J33	J18	=IF(AND(D18="Inventory",E18=$A$22),J17+H18,IF(OR(D18="",E18=""),"",IF(E18=$A$22,J17-H18,J17)))
K18:K33	K18	=IF(AND(D18="Inventory",E18=$A$23),K17+H18,IF(OR(D18="",E18=""),"",IF(E18=$A$23,K17-H18,K17)))

Cells with Data Validation
Cell	Allow	Criteria
D17:D44	List	=$A$6:$A$12
E17:E50	List	=$A$21:$A$31
F17	List	=$A$16:$A$18
F18:F22	List	=$A$16:$A$18
F23	List	=$A$16:$A$18
F24:F28	List	=$A$16:$A$18
F29	List	=$A$16:$A$18
F30:F34	List	=$A$16:$A$18

 

[jjp2985]

I ended up using what Johnny had helped with on utilizing the formula you gave me but showed me how to put my table name in there instead of the range so it is this

=IF(AND($D19="Inventory",$E19=INDEX(Cap_Type,COLUMNS($I:I))),I18+$H19,IF(OR($D19="",$E19=""),"",IF($E19=INDEX(Cap_Type,COLUMNS($I:I)),I18-$H19,I18)))

 

[Peter_SSs]

Thanks. If interested, I think this shorter version should do the same job.

21 08 15.xlsm
	A			D	E		G	H	I	J	K
15	Pack Type							1 Large bag has 10 single pack of caps in it
16	Large Bag			Department	Cap Type		Number of Pack(s)/Cap(s) Took/Received	Amount Small Packs Took	Remaing 2mL Prep Vial Black Cap Small Packs in Inventory	Remaing GC Vial Cap Small Packs in Inventory	Remaing 4mL Prep Vial Cap Small Packs in Inventory
17	Single Pack			Inventory	GC Vial Cap			1536	1536	1823	1158
18	Microcentrifuge Cap Bag			Commercial	2mL Prep Vial Black Cap		5	50	1486	1823	1158
19				Research	4mL Glass Vial Cap		11	110	1486	1823	1048
20	Cap Type			R&D	2mL Prep Vial Black Cap		23	230	1256	1823	1048
21	2mL Prep Vial Black Cap			UK	GC Vial Cap		42	420	1256	1403	1048
22	GC Vial Cap			Australia	4mL Glass Vial Cap		32	320	1256	1403	728
23	4mL Glass Vial Cap			Inventory	GC Vial Cap		200	2000	1256	3403	728
24	Test Tube Cap			Commercial	2mL Prep Vial Black Cap		5	50	1206	3403	728
25	LC Vial Cap			Research	4mL Glass Vial Cap		11	110	1206	3403	618
26	2mL Prep Vial Green Cap			R&D	2mL Prep Vial Black Cap		23	230	976	3403	618
27	Microcentrifuge Tube Red Caps			UK	GC Vial Cap		42	420	976	2983	618
28	Microcentrifuge Tube Yellow Caps			Australia	4mL Glass Vial Cap		32	320	976	2983	298
29	Microcentrifuge Tube Purple Caps			Inventory	GC Vial Cap		200	2000	976	4983	298
30	Microcentrifuge Tube Orange Caps			Commercial	2mL Prep Vial Black Cap		5	50	926	4983	298
31	Microcentrifuge Tube Clear Caps			Research	4mL Glass Vial Cap		11	110	926	4983	188
32				R&D	2mL Prep Vial Black Cap		23	230	696	4983	188
33				UK	GC Vial Cap		42	420	696	4563	188
jjp2985 (2)

Cell Formulas
Range	Formula
I18:K33	I18	=IF(OR($D18="",$E18=""),"",I17+$H18*IF($E18=INDEX(Cap_Type,COLUMNS($I:I)),IF($D18="Inventory",1,-1),0))