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.