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

 

[johnnyL]

Not sure why you would want to Replace the $A$21's with Offset, but:

Formula for replacing the $A$21's in I18 would be
=IF(AND(D18="Inventory",E18=OFFSET(E18,3,-4)),I17+H18,IF(OR(D18="",E18=""),"",IF(E18=OFFSET(E18,3, -4),I17-H18,I17)))

 

[jjp2985]

Johnny,

I am looking to do that as I want to drag this formula across the row and have it associate the correct item in the list that's in A21:A31 with the correct column instead of having to individually alter the formula for each column.

I can get it to work for a single cell but cannot figure out how to translate it across the row or down the column. You way works great for just that single cell but when I drag that formula to the columns to the right it does not translate correctly to those other columns. Also, when I drag that formula down it does not work either.

From that single cell, I18, I am wanting to drag this formula to the right and each column gets associated with a different object down the list. Then also I am going to drag this formula down and within that same column it needs to stay associated with that specific object.

 

[Peter_SSs]

OFFSET is a volatile function so in general is best avoided wherever a reasonable non-volatile option is available.
Try this in I18 and copy across and down

=IF(AND($D18="Inventory",$E18=INDEX($A$21:$A$31,COLUMNS($I:I))),I17+$H18,IF(OR($D18="",$E18=""),"",IF($E18=INDEX($A$21:$A$31,COLUMNS($I:I)),I17-$H18,I17)))

 

[jjp2985]

Thanks that worked out great. I'm fairly new with trying some complex operations within excel and learning my way thru creating this workbook I'm working on.

What is the INDEX function saying?

 

[Peter_SSs]

What is the INDEX function saying?

When the formula is in column I it says
INDEX($A$21:$A$31,COLUMNS($I:I))
COLUMNS($I:I) counts those columns = 1
INDEX($A$21:$A$31,1) = A21

When the formula is in column J it says
INDEX($A$21:$A$31,COLUMNS($I:J))
COLUMNS($I:J) counts those columns = 2
INDEX($A$21:$A$31,2) = A22

etc

 

[jjp2985]

Also with the INDEX function I see it is hard set to a range of values. Is there a way to leave it open or put in another function to as where if I were to expand that table to include more supplies it would accommodate them without me having to go in and changing the formula in the cell? So, lets say we obtain new items and I need to add 2 items to the list so now I need it to index to A33 instead of A31.

A21:A31 is a table with A20 as the Title if that helps with anything

 

[johnnyL]

=IF(AND($D18="Inventory",$E18=INDEX(NameOfNamedRange,COLUMNS($I:I))),I17+$H18,IF(OR($D18="",$E18=""),"",IF($E18=INDEX(NameOfNamedRange,COLUMNS($I:I)),I17-$H18,I17)))

 

[jjp2985]

Awesome, works perfect. Thank you all for the help.

 

[Peter_SSs]

You're welcome.

Out of interest, what formula did you end up with?