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.
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 |