utilizing OFFSET formula inside a formula to transpose list

jjp2985

New Member
Joined
Aug 13, 2021
Messages
12
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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
ABCDEFGHIJKLMN
15Pack Type1 single pack has 100 caps in it1 Large bag has 10 single pack of caps in it100 green caps = 10g
16Large BagDateInitialsDepartmentCap TypePack TypeNumber of Pack(s)/Cap(s) Took/ReceivedAmount Small Packs TookRemaing 2mL Prep Vial Black Cap Small Packs in InventoryRemaing GC Vial Cap Small Packs in InventoryRemaing 4mL Prep Vial Cap Small Packs in InventoryRemaing Test Tube Cap Packs in InventoryRemaing LC Vial Cap Small Packs in InventoryRemaing 2mL Prep Vial Green Caps in Inventory
17Single Pack12-Mar-2021JJPInventoryGC Vial Cap1536153618231158
18Microcentrifuge Cap Bag23-Feb-2021JJPCommercial2mL Prep Vial Black CapLarge Bag550148618231158
1921-Jan-2021Research4mL Glass Vial CapLarge Bag11110148618231048
20Cap Type12-Feb-2021R&D2mL Prep Vial Black CapLarge Bag23230125618231048
212mL Prep Vial Black Cap11-Jan-2021UKGC Vial CapLarge Bag42420125614031048
22GC Vial Cap21-Feb-2021Australia4mL Glass Vial CapLarge Bag3232012561403728
234mL Glass Vial Cap2-Apr-2021InventoryGC Vial CapLarge Bag200200012563403728
24Test Tube Cap3-May-2021Commercial2mL Prep Vial Black CapLarge Bag55012063403728
25LC Vial Cap2-Jun-2021Research4mL Glass Vial CapLarge Bag1111012063403618
262mL Prep Vial Green Cap5-Apr-2021R&D2mL Prep Vial Black CapLarge Bag232309763403618
27Microcentrifuge Tube Red Caps5-Apr-2021UKGC Vial CapLarge Bag424209762983618
28Microcentrifuge Tube Yellow Caps4-May-2021Australia4mL Glass Vial CapLarge Bag323209762983298
29Microcentrifuge Tube Purple Caps3-Jul-2021InventoryGC Vial CapLarge Bag20020009764983298
30Microcentrifuge Tube Orange Caps5-Jul-2021Commercial2mL Prep Vial Black CapLarge Bag5509264983298
31Microcentrifuge Tube Clear Caps3-Aug-2021Research4mL Glass Vial CapLarge Bag111109264983188
324-Sep-2021R&D2mL Prep Vial Black CapLarge Bag232306964983188
335-Sep-2021UKGC Vial CapLarge Bag424206964563188
Caps for Vials
Cell Formulas
RangeFormula
H18:H33H18=IF(F18=$A$16,G18*10,IF(F18=$A$18,G18*500,IF(F18=""," ",G18)))
I18:I33I18=IF(AND(D18="Inventory",E18=$A$21),I17+H18,IF(OR(D18="",E18=""),"",IF(E18=$A$21,I17-H18,I17)))
J18:J33J18=IF(AND(D18="Inventory",E18=$A$22),J17+H18,IF(OR(D18="",E18=""),"",IF(E18=$A$22,J17-H18,J17)))
K18:K33K18=IF(AND(D18="Inventory",E18=$A$23),K17+H18,IF(OR(D18="",E18=""),"",IF(E18=$A$23,K17-H18,K17)))
Cells with Data Validation
CellAllowCriteria
D17:D44List=$A$6:$A$12
E17:E50List=$A$21:$A$31
F17List=$A$16:$A$18
F18:F22List=$A$16:$A$18
F23List=$A$16:$A$18
F24:F28List=$A$16:$A$18
F29List=$A$16:$A$18
F30:F34List=$A$16:$A$18
 
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)))
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Thanks. If interested, I think this shorter version should do the same job.

21 08 15.xlsm
ADEGHIJK
15Pack Type1 Large bag has 10 single pack of caps in it
16Large BagDepartmentCap TypeNumber of Pack(s)/Cap(s) Took/ReceivedAmount Small Packs TookRemaing 2mL Prep Vial Black Cap Small Packs in InventoryRemaing GC Vial Cap Small Packs in InventoryRemaing 4mL Prep Vial Cap Small Packs in Inventory
17Single PackInventoryGC Vial Cap1536153618231158
18Microcentrifuge Cap BagCommercial2mL Prep Vial Black Cap550148618231158
19Research4mL Glass Vial Cap11110148618231048
20Cap TypeR&D2mL Prep Vial Black Cap23230125618231048
212mL Prep Vial Black CapUKGC Vial Cap42420125614031048
22GC Vial CapAustralia4mL Glass Vial Cap3232012561403728
234mL Glass Vial CapInventoryGC Vial Cap200200012563403728
24Test Tube CapCommercial2mL Prep Vial Black Cap55012063403728
25LC Vial CapResearch4mL Glass Vial Cap1111012063403618
262mL Prep Vial Green CapR&D2mL Prep Vial Black Cap232309763403618
27Microcentrifuge Tube Red CapsUKGC Vial Cap424209762983618
28Microcentrifuge Tube Yellow CapsAustralia4mL Glass Vial Cap323209762983298
29Microcentrifuge Tube Purple CapsInventoryGC Vial Cap20020009764983298
30Microcentrifuge Tube Orange CapsCommercial2mL Prep Vial Black Cap5509264983298
31Microcentrifuge Tube Clear CapsResearch4mL Glass Vial Cap111109264983188
32R&D2mL Prep Vial Black Cap232306964983188
33UKGC Vial Cap424206964563188
jjp2985 (2)
Cell Formulas
RangeFormula
I18:K33I18=IF(OR($D18="",$E18=""),"",I17+$H18*IF($E18=INDEX(Cap_Type,COLUMNS($I:I)),IF($D18="Inventory",1,-1),0))
 
Upvote 0

Forum statistics

Threads
1,223,939
Messages
6,175,529
Members
452,651
Latest member
wordsearch

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