SOLVED

Trying to Reference Table Columns in a Formula using Table/Column Name

%3CLINGO-SUB%20id%3D%22lingo-sub-3353319%22%20slang%3D%22en-US%22%3ETrying%20to%20Reference%20Table%20Columns%20in%20a%20Formula%20using%20Table%2FColumn%20Name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3353319%22%20slang%3D%22en-US%22%3E%3CP%3EWorking%20with%20Excel%20Online%20version...%20Office%20365%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20using%20the%20following%20formula%20in%20a%20cell%20on%20the%20same%20worksheet%20as%20the%20table%20I'm%20trying%20to%20reference%20in%20the%20formula...%3C%2FP%3E%3CP%3E%3DAVERAGE(IF(F2%3AF8470%26lt%3B%26gt%3B%22%22%2CH2%3AH8470-F2%3AF8470))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20some%20reason%20the%20formula%20isn't%20always%20updating.%20Of%20note%20is%20that%20there%20are%20dates%20in%20the%20two%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20it%20help%20if%20I%20used%20the%20Table%20Name%20and%20Column%20Names%3F%20I%20can't%20seem%20to%20get%20it%20to%20work%20like%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DAVERAGE(IF(tblCOMPLETE%5BDate%20BPC003F%20Recieved%5D%26lt%3B%26gt%3B%22%22%2CtblCOMPLETE%5BCompleted%20Date%5D-tblCOMPLETE%5BDate%20BPC003F%20Recieved%5D))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20a%20problem%20to%20have%20spaces%20in%20the%20column%20names%3F%20I'm%20not%20sure%20what%20I'm%20doing%20wrong%20here.%20Can%20someone%20see%20the%20problem%3F%20Thanks!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3353319%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3353397%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20Reference%20Table%20Columns%20in%20a%20Formula%20using%20Table%2FColumn%20Name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3353397%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1348252%22%20target%3D%22_blank%22%3E%40Marcus_Booth%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20pointing%20to%20the%20ranges%20when%20you%20create%20the%20formula.%20It%20should%20work%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S1421.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F370562iC9A56995561B8AB9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S1421.png%22%20alt%3D%22S1421.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3353521%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20Reference%20Table%20Columns%20in%20a%20Formula%20using%20Table%2FColumn%20Name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3353521%22%20slang%3D%22en-US%22%3EWhen%20I%20paste%20this%20very%20formula%20into%20the%20cell%2C%20Excel%20puts%20a%20little%20hash%20before%20the%20%3D%20sign.%20It%20comes%20back%20when%20I%20delete%20it%20and%20the%20formula%20is%20not%20active.%20I'm%20using%20the%20Web%20app.%20I%20don't%20get%20any%20error%20messages.%20It's%20as%20if%20it%20thinks%20I'm%20just%20typing%20text.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3353571%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20Reference%20Table%20Columns%20in%20a%20Formula%20using%20Table%2FColumn%20Name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3353571%22%20slang%3D%22en-US%22%3ESo%20sorry!%20Someone%20had%20misspelled%20the%20table%20name.%20Working%20great%20now!%20Thank%20you!!!%3C%2FLINGO-BODY%3E
Occasional Contributor

Working with Excel Online version... Office 365

 

I'm using the following formula in a cell on the same worksheet as the table I'm trying to reference in the formula...

=AVERAGE(IF(F2:F8470<>"",H2:H8470-F2:F8470))

 

For some reason the formula isn't always updating. Of note is that there are dates in the two columns.

 

Would it help if I used the Table Name and Column Names? I can't seem to get it to work like this...

 

=AVERAGE(IF(tblCOMPLETE[Date BPC003F Recieved]<>"",tblCOMPLETE[Completed Date]-tblCOMPLETE[Date BPC003F Recieved]))

 

Is it a problem to have spaces in the column names? I'm not sure what I'm doing wrong here. Can someone see the problem? Thanks! 

 

 

3 Replies
best response confirmed by Marcus_Booth (Occasional Contributor)
Solution

@Marcus_Booth 

Try pointing to the ranges when you create the formula. It should work:

S1421.png

When I paste this very formula into the cell, Excel puts a little hash before the = sign. It comes back when I delete it and the formula is not active. I'm using the Web app. I don't get any error messages. It's as if it thinks I'm just typing text.
So sorry! Someone had misspelled the table name. Working great now! Thank you!!!