SOLVED
Home

Formula Help - Square footage conversion

%3CLINGO-SUB%20id%3D%22lingo-sub-363838%22%20slang%3D%22en-US%22%3EFormula%20Help%20-%20Square%20footage%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363838%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20currently%20have%20a%20formula%20that%20is%20working%20in%20cell%20F4.%20This%20cell%20takes%20the%20Item%20description%20in%20cell%20A3%2C%20compares%20it%20to%20a%20data%20tab%2C%20and%20if%20those%20two%20match%20it%20gives%20me%20the%20square%20footage%20conversion%20listed%20for%20that%20specific%20text%20multiplied%20by%20the%20quantity%20shipped%20(Cell%20B4).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20im%20having%20is%20when%20i%20drag%20the%20formula%20down%2C%20it%20wants%20to%20read%20one%20cell%20below%20the%20cell%20its%20designed%20to%20read%20(Cell%20F3)%20is%20there%20a%20way%20i%20can%20have%20every%20cell%20in%20column%20F%20read%20F2%20instead%20of%20going%20F3%2CF4%2CF5%2Cetc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20included%20my%20workbook%20and%20highlighted%20the%20correct%20cell%20with%20the%20working%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-363838%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363860%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%20-%20Square%20footage%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363860%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20welcome.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363857%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%20-%20Square%20footage%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363857%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20may%20simplify%20your%20formulas%20a%20bit%3A%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(Table1%2CMATCH(%24A4%2CTable1%5BITEM%5D%2C0)%2CMATCH(%24F%242%2CTable1%5B%23Headers%5D%2C0))%2C0)*B4%3C%2FPRE%3E%0A%3CP%3Eand%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(Table1%2CMATCH(%24A4%2CTable1%5BITEM%5D%2C0)%2CMATCH(%24E%244%2CTable1%5B%23Headers%5D%2C0))%2C0)*B4%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363855%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%20-%20Square%20footage%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363855%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you!%20I%20forgot%20about%20those%20silly%20%24%20signs.%20This%20has%20resolved%20my%20issue.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363851%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%20-%20Square%20footage%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363851%22%20slang%3D%22en-US%22%3EInsert%20%24%20before%20the%20row%20number%2C%20like%20this%3A%3CBR%20%2F%3EF%242%3C%2FLINGO-BODY%3E
Amanda Rotger
New Contributor

Hello all,

 

I currently have a formula that is working in cell F4. This cell takes the Item description in cell A3, compares it to a data tab, and if those two match it gives me the square footage conversion listed for that specific text multiplied by the quantity shipped (Cell B4).

 

The issue im having is when i drag the formula down, it wants to read one cell below the cell its designed to read (Cell F3) is there a way i can have every cell in column F read F2 instead of going F3,F4,F5,etc.

 

 

I have included my workbook and highlighted the correct cell with the working formula.

4 Replies
Solution
Insert $ before the row number, like this:
F$2

Thank you! I forgot about those silly $ signs. This has resolved my issue.

You may simplify your formulas a bit:

=IFERROR(INDEX(Table1,MATCH($A4,Table1[ITEM],0),MATCH($F$2,Table1[#Headers],0)),0)*B4

and

=IFERROR(INDEX(Table1,MATCH($A4,Table1[ITEM],0),MATCH($E$4,Table1[#Headers],0)),0)*B4

 

You’re welcome.
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies